Jerry Abraham Jerry Abraham - 4 months ago 21
SQL Question

Dormant users with filter days in prestashop 1.6

I am trying to add below query in $this_select with left join but not working properly

Below is my working query which works fine :

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'


But my problem is that when I placed below query code in my controller it is not taking the first and the second left 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 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.'';


Am I doing anything wrong in the above $this_select or $this_join ??
Bleow is db exception which I get the problem is that I am not seeing my first two joins here ie it is not taking the first two joins

enter image description here

Answer

You're overriding the _join value on each call to $this->_join =. You should use $this->_join .= for the second and last 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;
Comments