Tip: using PrestaShop's SQL Manager to export detailed orders - Configuring and using PrestaShop - PrestaShop

Tip: using PrestaShop's SQL Manager to export detailed orders - Configuring and using PrestaShop - PrestaShop

I use it to write a similar sql, in spanish and group only by d.id_order.

In this form don´t duplicate and don´t erase any product. if you need to see all the products and all the orders, you can get it with a code like this:

SELECT d.id_order, os.name AS estado, o.date_upd AS fecha, d.product_name AS producto, d.product_reference AS ref_ReleMat, d.product_supplier_reference AS ref_proveedor, d.product_quantity AS uds,  d.product_price, s.quantity AS en_stock, o.payment,  CONCAT_WS(  ' ', g.firstname, g.lastname ) AS cliente,  CONCAT_WS(' ', ad.address1, ad.address2, ad.postcode, ad.city, ad.other, ad.phone, ad.phone_mobile) AS envio, CONCAT_WS(' ', ai.address1, ai.address2,  ai.postcode, ai.city, ai.other, ai.phone, ai.phone_mobile) AS facturacion, g.email, gl.name AS grupo
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_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE os.id_lang =1
GROUP BY d.id_order, d.product_name
ORDER BY d.id_order DESC

I use it in PS1.5 and it´s ok.