SQL Query for exporting Abandoned carts only from logged in users from X date to Y date

[calichelozano]

Hi, I've found similar topics for this, but they don't work exactly for what I need them.

I need the SQL query for exporting abandoned carts from logged in users, no guests, from X date to Y date, because what I really need is their email and date, a plus would be specyfing the abandoned cart amount (money).

Thank you! 

[Knowband Plugins]

Hi,

You can use the below query to fetch the data of the abandoned carts, especially the email, the date of cart was created, and the cost of the cart.

SELECT
c.id_cart AS cart_id,
cu.email AS customer_email,
c.date_add AS cart_creation_date,
SUM(cp.quantity * p.price) AS cart_total
FROM
ps_cart c
JOIN
ps_customer cu ON c.id_customer = cu.id_customer
LEFT JOIN
ps_cart_product cp ON c.id_cart = cp.id_cart
LEFT JOIN
ps_product p ON cp.id_product = p.id_product
WHERE
c.id_customer > 0
AND NOT EXISTS (
SELECT 1
FROM ps_orders o
WHERE o.id_cart = c.id_cart
)
AND c.date_add BETWEEN '2023-01-01 00:00:00' AND '2025-12-31 23:59:59' -- Specify your date range
GROUP BY
c.id_cart, cu.email, c.date_add
ORDER BY
c.date_add DESC;

Regards.
Nikhil    

[calichelozano]

4 minutes ago, Knowband Plugins said:

Hi,

You can use the below query to fetch the data of the abandoned carts, especially the email, the date of cart was created, and the cost of the cart.

SELECT
c.id_cart AS cart_id,
cu.email AS customer_email,
c.date_add AS cart_creation_date,
SUM(cp.quantity * p.price) AS cart_total
FROM
ps_cart c
JOIN
ps_customer cu ON c.id_customer = cu.id_customer
LEFT JOIN
ps_cart_product cp ON c.id_cart = cp.id_cart
LEFT JOIN
ps_product p ON cp.id_product = p.id_product
WHERE
c.id_customer > 0
AND NOT EXISTS (
SELECT 1
FROM ps_orders o
WHERE o.id_cart = c.id_cart
)
AND c.date_add BETWEEN '2023-01-01 00:00:00' AND '2025-12-31 23:59:59' -- Specify your date range
GROUP BY
c.id_cart, cu.email, c.date_add
ORDER BY
c.date_add DESC;

Regards.
Nikhil    

Hi Nikhil! Thank you for replying! I tested your code but once I save the SQL Query it gives me this error: 

"Type error: Argument 1 passed to PrestaShop\PrestaShop\Adapter\SqlManager\SqlQueryValidator::getWhereKeywordError() must be of the type array, bool given, called in .../public_html/src/Adapter/SqlManager/SqlQueryValidator.php on line 92

[Symfony\Component\Debug\Exception\FatalThrowableError 0]"

Please let me know, thanks!

[Knowband Plugins]

Hi,

It seems that you have converted your query into PS code.

Could you please share what code/query you have made so that we can check the same.

Regards.

[calichelozano]

39 minutes ago, Knowband Plugins said:

Hi,

It seems that you have converted your query into PS code.

Could you please share what code/query you have made so that we can check the same.

Regards.

Hi! Sorry, I'm not understanding you, I just copied your code and executed, what do you mean converting into PS code?

 

Thanks for the patience!

[Knowband Plugins]

Hi,

Thank you for clarifying! The error indicates that PrestaShop's SQL Manager might be interpreting the query incorrectly, possibly due to certain clauses or syntax that the SQL Manager doesn't fully support.

Try running the query directly in your database management tool (e.g., phpMyAdmin, MySQL Workbench) to verify the results.

Regards.