MySQL products by orders

[Cherniakovsky]

Hello, I am doing a type of sales report through a mysql query. The objective is to create a line for each product (with its corresponding attribute) within a purchase order. With my current query I am able to bring some information, but it is not right. For example, I get multiples for a purchase order, the products are listed but these are repeated by their attributes, and not all the products of the order come out. The mysql query is as follows. Thanks to who can hel me to solve this issue.

select distinctrow o.date_add, o.reference, c.firstname, c.lastname, pa.city, ppl.name,
                   GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") AS combinations,
                   carrier.name, o.payment, p.price ,o.total_discounts, o.valid
from ps_orders as o
join ps_customer as c on o.id_customer = c.id_customer
join ps_cart as cart on cart.id_cart = o.id_cart
join ps_cart_product as cartProduct on o.id_cart = cartProduct.id_cart
join ps_product as p on cartProduct.id_product = p.id_product
join ps_product_lang ppl on ppl.id_product = cartProduct.id_product
join ps_address pa on c.id_customer = pa.id_customer
join ps_carrier carrier on cart.id_carrier = carrier.id_carrier
join ps_product_attribute prodatt on prodatt.id_product = cartProduct.id_product
join ps_product_attribute_combination ppac on prodatt.id_product_attribute = ppac.id_product_attribute
join ps_attribute a on ppac.id_attribute = a.id_attribute
join ps_attribute_lang pal on a.id_attribute = pal.id_attribute
join ps_order_detail pod on o.id_order = pod.id_order
where pod.id_order = o.id_order
GROUP BY ppac.id_product_attribute

 

[EvaF]

I don't know if it is exactly one you want to achive but you can try this:

select distinctrow o.date_add, o.reference, c.firstname, c.lastname, pa.city, ppl.name,
                   GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") AS combinations,
                   carrier.name, o.payment, p.price ,o.total_discounts, o.valid
from ps_orders as o
join ps_order_detail pod on o.id_order = pod.id_order
join ps_customer as c on o.id_customer = c.id_customer

/*
join ps_cart as cart on cart.id_cart = o.id_cart
join ps_cart_product as cartProduct on o.id_cart = cartProduct.id_cart
join ps_product as p on cartProduct.id_product = p.id_product
-- here i don't know why do not you use directly ps_order_detail.product_id
*/
join ps_product as p on pod.product_id = p.id_product
/*  join ps_product_lang ppl on ppl.id_product = cartProduct.id_product */
join ps_product_lang ppl on ppl.id_product = p.id_product

/*join ps_address pa on c.id_customer = pa.id_customer
--- customer can have defined more addresses - better is to take it from order - either id_address_delivery or id_address_invoice*/
join ps_address pa on o.id_address_delivery = pa.id_address

/* join ps_carrier carrier on cart.id_carrier = carrier.id_carrier  - it is possible to take from ps_order.id_carrier*/
join ps_carrier carrier on o.id_carrier = carrier.id_carrier

/* join ps_product_attribute prodatt on prodatt.id_product = cartProduct.id_product 
-- better is imho to read from ps_order_detail.product_attribute_id */
join ps_product_attribute prodatt on prodatt.id_product_attribute = pod.product_attribute_id

join ps_product_attribute_combination ppac on prodatt.id_product_attribute = ppac.id_product_attribute
/*  join ps_attribute a on ppac.id_attribute = a.id_attribute - it is useles join */

join ps_attribute_lang pal on ppac.id_attribute = pal.id_attribute

/*join ps_order_detail pod on o.id_order = pod.id_order  -- this join is placed higher*/
where 
/* pod.id_order = o.id_order this condition is  set in JOIN */
pal.id_lang = 1 and ppl.id_lang = 1
/* later replace in php by pal.id_lang = ' . $this->context->id_lang .' and ppl.id_lang = ' . $this->context->id_lang  */
and o.date_add >= '2020-07-01'
/* replace this condition to restrict result - otherwise you have got all orders */
GROUP BY ppac.id_product_attribute

 

[Cherniakovsky]

Thanks for your response!

It's look like i resolve the another day with this query. I put your where sentence date_add too and add valid as 1 to take only the orders was finished. Whatever I put my query too if another person needs that.
 

SELECT DATE_FORMAT(o.date_add, "%d-%m-%Y") as Date,
       o.reference as "Order",
       concat(pc.firstname, ' ', pc.lastname ) as Nname,
       pa.city as City,
       d.product_name as Product,
       c.name as Carrier,
       o.payment as "payment",
       d.product_price as Price
FROM ps_order_detail d
LEFT JOIN ps_orders o ON o.id_order = d.id_order
left join ps_customer pc on pc.id_customer = o.id_customer
LEFT JOIN ps_product_shop ps ON ps.id_product = d.product_id AND ps.id_shop=o.id_shop
join ps_address pa on pc.id_customer = pa.id_customer
join ps_carrier c on o.id_carrier = c.id_carrier
WHERE o.valid = 1 and o.date_add >= '2020-07-01'
GROUP BY product_attribute_id