Florent Posted October 3, 2023 Share Posted October 3, 2023 Bonjour, J'ai une requête sql qui sort la liste des clients avec diverses infos. Je voudrais l'adapter en ajoutant la date de la dernière commande passée par chaque client pour les clients ayant déjà commandé, et laisser nul pour les autres. Je cherche depuis plusieurs jours en vain. Alors, si quelqu'un s'y connait un peu ... Merci Voici ma requêté initiale : SELECT a.`id_customer`, `firstname`, `lastname`, `email`, a.`active` AS `active`, `newsletter`, `optin` , a.date_add, gl.name as title, ( SELECT SUM(total_paid_real / conversion_rate) FROM ps_orders o WHERE o.id_customer = a.id_customer AND o.id_shop IN (1) AND o.valid = 1 ) as total_spent, ( SELECT c.date_add FROM ps_guest g LEFT JOIN ps_connections c ON c.id_guest = g.id_guest WHERE g.id_customer = a.id_customer ORDER BY c.date_add DESC LIMIT 1 ) as connect FROM `ps_customer` a LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1) WHERE 1 AND a.`deleted` = 0 ORDER BY `date_add` DESC LIMIT 0, 50 Link to comment Share on other sites More sharing options...
khanzain Posted January 25, 2024 Share Posted January 25, 2024 It looks like you want to modify your SQL query to include the date of the last order for customers who have placed orders and leave it as null for those who haven't. To achieve this, you can use a LEFT JOIN with the ps_orders table to get the information about the last order for each customer. Here's an adapted version of your query: SELECT a.`id_customer`, `firstname`, `lastname`, `email`, a.`active` AS `active`, `newsletter`, `optin`, a.date_add, gl.name AS title, (SELECT SUM(total_paid_real / conversion_rate) FROM ps_orders o WHERE o.id_customer = a.id_customer AND o.id_shop IN (1) AND o.valid = 1) AS total_spent, MAX(o.date_add) AS last_order_date, (SELECT c.date_add FROM ps_guest g LEFT JOIN ps_connections c ON c.id_guest = g.id_guest WHERE g.id_customer = a.id_customer ORDER BY c.date_add DESC LIMIT 1) AS connect FROM `ps_customer` a LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1) LEFT JOIN ps_orders o ON a.id_customer = o.id_customer WHERE 1 AND a.`deleted` = 0 GROUP BY a.`id_customer` ORDER BY `date_add` DESC LIMIT 0, 50; In this modified query, I added a LEFT JOIN with the ps_orders table and used the MAX(o.date_add) to get the date of the last order for each customer. The GROUP BY a.id_customer ensures that you get only one row per customer. The LEFT JOIN ensures that customers without orders will still be included in the result set with last_order_date as null. Link to comment Share on other sites More sharing options...
Florent Posted June 2, 2024 Author Share Posted June 2, 2024 Sorry for my late reply. But thank you very much for your help. Your SQL query does exactly what I need. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now