requête SQL - export des produits vendus

[Julien.G]

Bonjour,

 

J'ai actuellement une requête qui me permets d'exporter les produits vendus avec plusieurs infos comme nom du client, quantités, date, id de commande etc...

Je souhaiterai ajouter le nom de la boutique sur laquelle l'achat à été fait car j'ai 3 boutiques en multiboutique, voilà la requête actuelle :

SELECT
   d.id_order_detail,
   d.id_order,
   o.date_add,
   CONCAT_WS(' ', g.firstname, g.lastname) AS customer,
   g.email,
   os.name AS state,
   d.product_name,
   d.product_reference,
   d.product_quantity,
   d.product_price,
   o.payment,
   c.name AS carrier_name,
   CONCAT_WS(' ', a.lastname, a.firstname, a.address1, a.address2, a.postcode, a.city) AS address_delivery,
   CONCAT_WS(' ', a.lastname, a.firstname, a.address1, a.address2, a.postcode, a.city) AS address_invoice,
   REPLACE(IFNULL(GROUP_CONCAT(cd.value), ''), '"', '\'') AS customized_data 
FROM
   ps_order_detail d 
   LEFT JOIN
      ps_orders o 
      ON (d.id_order = o.id_order) 
   LEFT JOIN
      ps_customer g 
      ON (o.id_customer = g.id_customer) 
   LEFT JOIN
      ps_carrier c 
      ON (o.id_carrier = c.id_carrier) 
   LEFT JOIN
      ps_order_state_lang os 
      ON (o.current_state = os.id_order_state) 
   LEFT JOIN
      ps_address a 
      ON (a.id_address = o.id_address_delivery) 
   LEFT JOIN
      ps_address ab 
      ON (ab.id_address = o.id_address_invoice) 
   LEFT JOIN
      ps_customization cu 
      ON (cu.id_cart = o.id_cart) 
   LEFT JOIN
      ps_customized_data cd 
      ON (cd.id_customization = cu.id_customization) 
WHERE
   os.id_lang = 1 
   AND YEAR(o.date_add) = 2023 
GROUP BY
   d.id_order_detail 
ORDER BY
   d.id_order_detail DESC

Quelqu'un pourrait-il m'aiguiller svp ?

 

Je vous remercie,

 

Julien

[EISEAL]

Bonjour,

essaie de jointer la table ps_shop : "ps_shop sh ON (sh.id_shop = o.id_shop)"  

et en sélectionnant le nom de la boutique : "sh.name AS shop_name" dans le SELECT comme ça :

 

SELECT
   d.id_order_detail,
   d.id_order,
   o.date_add,
   CONCAT_WS(' ', g.firstname, g.lastname) AS customer,
   g.email,
   os.name AS state,
   d.product_name,
   d.product_reference,
   d.product_quantity,
   d.product_price,
   o.payment,
   c.name AS carrier_name,
   sh.name AS shop_name,
   CONCAT_WS(' ', a.lastname, a.firstname, a.address1, a.address2, a.postcode, a.city) AS address_delivery,
   CONCAT_WS(' ', a.lastname, a.firstname, a.address1, a.address2, a.postcode, a.city) AS address_invoice,
   REPLACE(IFNULL(GROUP_CONCAT(cd.value), ''), '"', '\'') AS customized_data 
FROM
   ps_order_detail d 
   LEFT JOIN
      ps_orders o 
      ON (d.id_order = o.id_order) 
   LEFT JOIN
      ps_customer g 
      ON (o.id_customer = g.id_customer) 
   LEFT JOIN
      ps_carrier c 
      ON (o.id_carrier = c.id_carrier) 
   LEFT JOIN
      ps_order_state_lang os 
      ON (o.current_state = os.id_order_state) 
   LEFT JOIN
      ps_address a 
      ON (a.id_address = o.id_address_delivery) 
   LEFT JOIN
      ps_address ab 
      ON (ab.id_address = o.id_address_invoice) 
   LEFT JOIN
      ps_customization cu 
      ON (cu.id_cart = o.id_cart) 
   LEFT JOIN
      ps_customized_data cd 
      ON (cd.id_customization = cu.id_customization) 
   LEFT JOIN
      ps_shop sh 
      ON (sh.id_shop = o.id_shop) 
WHERE
   os.id_lang = 1 
   AND YEAR(o.date_add) = 2023 
GROUP BY
   d.id_order_detail 
ORDER BY
   d.id_order_detail DESC

 

[Julien.G]

Bonjour et merci pour ta réponse.

J'ai complètement oublié de répondre à ce sujet, je suis finalement partit sur une autre requête, le nom de la boutique et listé dans un colonne, il me reste à filtrer dans le fichier obtenu.

 

J'aurais bien partagé pour les autres mais je ne peux pas, le site me bloque, je ne comprends pas