Abandoned Carts Mysql Query

[ilmikhail]

Greetings,

 

My client needs a report using MySQL , this report must have all the abandoned carts from the store , I dont know what field or condition should I have to use to obtain this query.

 

Any advice or comment would be appreciated.

 

Thanks.

[Alphaloc]

You basically want all the carts from the ps_cart table that do not have a corresponding record in the ps_orders table (carts without a linked order is an abandoned cart) The query below will give you those rows.

SELECT c.* 
FROM ps_cart c
LEFT JOIN ps_orders o ON ( c.id_cart = o.id_cart ) 
WHERE o.id_order IS NULL 
ORDER BY c.id_cart ASC

[LOCEN_]

Hi, i need same thing but with orders and customers informations.

Can someone help me?

Edited September 8, 2016 by LOCEN_ (see edit history)

[nawres]

This request gives us all abandoned carts without date limit, if you want you can give an interval of time to execute this one:

select c.email , a.id_cart,a.date_add,
sum(if((IFNULL(cp.quantity,0) || ifnull(p.price,0)), cp.quantity*p.price,null)) as price
 from ps_cart as a 
 LEFT JOIN ps_customer c ON (c.id_customer = a.id_customer)
 LEFT JOIN ps_currency cu ON (cu.id_currency = a.id_currency) 
 LEFT JOIN ps_carrier ca ON (ca.id_carrier = a.id_carrier) 
 LEFT JOIN ps_orders o ON (o.id_cart = a.id_cart) 
  LEFT JOIN ps_cart_product cp ON (cp.id_cart = a.id_cart) 
  LEFT JOIN ps_product p ON (cp.id_product = p.id_product) 

 LEFT JOIN ( SELECT id_guest FROM ps_connections WHERE TIME_TO_SEC(TIMEDIFF(CURRENT_TIMESTAMP, date_add)) < 1800 LIMIT 1 ) AS co
 ON co.id_guest = a.id_guest
 where  isnull(id_order)
 group by id_cart;

 

It works for me   

Edited November 19, 2021 by nawres (see edit history)

[Prestachamps]

Hi, 

one small correction here to the SQL query, which will return correctly the price of combinations also not only the main product's price:

select c.email , a.id_cart,a.date_add,
/*sum(if((IFNULL(cp.quantity,0) || ifnull(p.price,0)), cp.quantity*p.price,null)) as price,*/
 if( sum(if((IFNULL(cp.quantity,0) || ifnull(p.price,0)), cp.quantity*p.price,null))+0 = 0 , sum(if((IFNULL(cp.quantity,0) || ifnull(p.price,0)), cp.quantity*pa.price,null))  , sum(if((IFNULL(cp.quantity,0) || ifnull(p.price,0)), cp.quantity*p.price,null)) ) as price
 from ps_cart as a 
 LEFT JOIN ps_customer c ON (c.id_customer = a.id_customer)
 LEFT JOIN ps_currency cu ON (cu.id_currency = a.id_currency) 
 LEFT JOIN ps_carrier ca ON (ca.id_carrier = a.id_carrier) 
 LEFT JOIN ps_orders o ON (o.id_cart = a.id_cart) 
  LEFT JOIN ps_cart_product cp ON (cp.id_cart = a.id_cart) 
  LEFT JOIN ps_product p ON (cp.id_product = p.id_product) 
LEFT JOIN ps_product_attribute pa ON (pa.id_product = p.id_product  AND pa.id_product_attribute = cp.id_product_attribute )
 LEFT JOIN ( SELECT id_guest FROM ps_connections WHERE TIME_TO_SEC(TIMEDIFF(CURRENT_TIMESTAMP, date_add)) < 1800 LIMIT 1 ) AS co
 ON co.id_guest = a.id_guest
 where  isnull(id_order)
 group by id_cart;

Kind regards, Leo.

[sparh]

Hello @Prestachamps & @nawres

thanks for the query it is a good start but I don't get the product attribute price I  still get the main product price. (tried both queries)
Do you have any idea how to get the price of the product attribute chosen in the cart ?

Actually I would like the query to display what we see in the admin cart page

Thanks

[ps8modules]

Hi.

Does the client want to see the same overview as in the "Shopping Carts" section? Why is a sql query needed? Isn't it easier to use your own module? Prestashop includes many functions that will return results. The module can contain a clear table including export to csv.

Rules, discounts, etc. can also be set in the cart, and it is almost impossible to get it using sql.

[sparh]

Hi 

actually I want to export carts infos on a monthly basis to make statistics.
Moreover I need to delete some customer ids from the list.

Here are what I would need
is guest or customer name
is abandonned / order id
product(s) amount / order amount
cart date