在prestashop 1.6中具有过滤天数的休眠用户

Jerry Abraham 发表于 Dev

杰里·亚伯拉罕

我正在尝试使用左联接在$ this_select中添加以下查询，但无法正常工作

下面是我的工作查询，它工作正常：

  select      a.id_customer as id_customer,
        a.id_shop,
        a.email,
        a.lastname,
        a.firstname,
        max(c.date_add) as last_visit,
        IFNULL(max(b.date_add),'1001-01-01 00:00:00') as Last_order_date
 from        ps_customer a
 left join  ps_orders b
  on          a.id_customer = b.id_customer
 left join   ps_guest g 
 on          a.id_customer = g.id_customer
left join   ps_connections c
on          g.id_guest = c.id_guest
group by    a.id_customer
 having      to_days(Last_order_date) < to_days(now())- '30'

但是我的问题是，当我在控制器中的查询代码下方放置时，它没有采用第一个和第二个左连接：

    $this->_select='        
    a.id_shop,
    a.email,
    a.lastname,
    a.firstname,
    max(c.date_add) as last_visit,
    IFNULL(max(b.date_add),"'.$default_date.'") as Last_order_date
    ';

    $this->_join = '
    LEFT JOIN `'._DB_PREFIX_.'orders` b ON (a.`id_customer` =b.`id_customer`)';

    $this->_join ='left join   ps_guest g 
    on (a.id_customer = g.id_customer)';

    $this->_join ='left join   ps_connections c
    ON    (   g.id_guest = c.id_guest)      
    group by    a.id_customer
    having      to_days(Last_order_date) < to_days(now())- '.$dormant_filter_days.'';

我在上面的$ this_select或$ this_join中做错了吗？Bleow是数据库异常，我得到的问题是我没有在这里看到我的前两个联接，即它没有采用前两个联接

弗洛里安·勒迈特（Florian Lemaitre）

您将覆盖_join每次调用的值$this->_join =。您应该使用$this->_join .=第二个也是最后一个联接。

$this->_select = '          
    a.id_shop,
    a.email,
    a.lastname,
    a.firstname,
    MAX(c.date_add) AS last_visit,
    IFNULL(MAX(b.date_add), "' . $default_date . '") AS Last_order_date';

$this->_join = 'LEFT JOIN `' . _DB_PREFIX_ . 'orders` b
    ON (a.`id_customer` = b.`id_customer`)';

$this->_join .= ' LEFT JOIN `' . _DB_PREFIX_ . 'guest` g 
    ON (a.id_customer = g.id_customer)';

$this->_join .= ' LEFT JOIN `' . _DB_PREFIX_ . 'connections` c
    ON (g.id_guest = c.id_guest)';

$this->_group = 'GROUP BY a.id_customer';

$this->_having = 'HAVING TO_DAYS(Last_order_date) < TO_DAYS(NOW()) - ' . $dormant_filter_days;