Jump to content

Recommended Posts

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

  • 3 months later...

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

  • 4 months later...

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...