Module - last order date per customer

[LauraPresta]

Hello guys,

 

Im looking for a way to get a .csv containing customers list with the date of their last order (i think it is simple a sql request)

 

Someone got an idea or able to do this simple module ?

[w3bsolutions]

This SQL query will give you that. You can then export to CSV using your DB manager. 

SELECT c.id_customer, c.firstname, c.lastname, orders.date_add AS last_order
FROM ps_customer c 
LEFT JOIN ( 
	SELECT o.id_customer, o.date_add 
	FROM ps_orders o 
	INNER JOIN (
		SELECT id_customer, max(date_add) as mostRecent
		FROM ps_orders
		GROUP BY id_customer 
	) ord ON o.id_customer = ord.id_customer AND o.date_add = ord.mostRecent 
) orders ON c.id_customer = orders.id_customer

Customers that have not placed an order will have a null value on last_order field.

[LauraPresta]

This is working nicely, thank you fire2 ! It should be usefull for a lot of people to chose customers for newsletter

[endurer]

This SQL query will give you that. You can then export to CSV using your DB manager. 

SELECT c.id_customer, c.firstname, c.lastname, orders.date_add AS last_order
FROM ps_customer c 
LEFT JOIN ( 
	SELECT o.id_customer, o.date_add 
	FROM ps_orders o 
	INNER JOIN (
		SELECT id_customer, max(date_add) as mostRecent
		FROM ps_orders
		GROUP BY id_customer 
	) ord ON o.id_customer = ord.id_customer AND o.date_add = ord.mostRecent 
) orders ON c.id_customer = orders.id_customer

Customers that have not placed an order will have a null value on last_order field.

Hi there, I am getting the following error in sql manager:

 

"Undefined "checkedSelect" error"

[w3bsolutions]

Hi there, I am getting the following error in sql manager:

 

"Undefined "checkedSelect" error"

 

which version of PS?

[endurer]

On 15/10/2017 at 3:39 AM, w3bsolutions said:

 

which version of PS?

1.6.1.17

Thanks

[w3bsolutions]

3 hours ago, endurer said:

1.6.1.17

Thanks

 

The query runs fine on any DB manager (e.g., PhpMyAdmin), not the Prestashop backend SQL query manager. I just tried it on a PS 1.6.1.17 shop. 

[endurer]

On 24/10/2017 at 4:41 AM, w3bsolutions said:

 

The query runs fine on any DB manager (e.g., PhpMyAdmin), not the Prestashop backend SQL query manager. I just tried it on a PS 1.6.1.17 shop. 

Thanks!