Prestashop order products sql

Question

Where I can find Prestashop SQL query where they display the products that were bought in order.

Like this:

I'm creating a custom page that shows 100 orders in table. and if clicked on order its shows the products that were ordered. I made an sql query:

SELECT product_id, product_reference AS kood,product_name AS nimetus,product_quantity AS nr, pl.link_rewrite,psa.quantity as kogukogus,group_concat(ps.product_supplier_reference) as supp_ref, p.location AS asukoht,
         CONCAT(c.link_rewrite,'/',p.id_product,'-',pl.link_rewrite,'.html') link
        FROM ps_order_detail o
        LEFT JOIN ps_product_lang pl on o.product_id = pl.id_product
        LEFT JOIN ps_product p on p.id_product = pl.id_product
        LEFT JOIN ps_stock_available psa on p.id_product = psa.id_product
        LEFT JOIN ps_category_lang c on c.id_category = p.id_category_default
        LEFT JOIN ps_product_supplier ps on p.id_product = ps.id_product
        WHERE pl.id_lang=2 AND c.id_lang=2 AND id_order= '".$q."'
        GROUP BY product_id, kood,nimetus,nr,pl.link_rewrite, kogukogus,asukoht, link

But when product has attributes it duplicates products.

In this case iButton has two colors black and red(attributes). In this order only one black iButton was bought with quantity of 20, but It shows three rows.

I tried to fix this, but that arised another problems with my sql query, that when it has no attribute it dosen't show anything.

SELECT product_id, product_reference AS kood,product_name AS nimetus,product_quantity AS nr, pl.link_rewrite,psa.quantity as kogukogus,group_concat(ps.product_supplier_reference) as supp_ref, p.location AS asukoht,
         CONCAT(c.link_rewrite,'/',p.id_product,'-',pl.link_rewrite,'.html') link
        FROM ps_order_detail o
        JOIN ps_product_lang pl on o.product_id = pl.id_product
        JOIN ps_product p on p.id_product = pl.id_product
        JOIN ps_stock_available psa on p.id_product = psa.id_product
        JOIN ps_category_lang c on c.id_category = p.id_category_default
        JOIN ps_product_supplier ps on p.id_product = ps.id_product
        LEFT JOIN ps_product_attribute pa on p.id_product=pa.id_product
        WHERE pl.id_lang=2 AND pa.id_product_attribute = psa.id_product_attribute AND pa.id_product_attribute =o.product_attribute_id  AND c.id_lang=2 AND id_order= '".$q."'
        GROUP BY product_id, kood,nimetus,nr,pl.link_rewrite, kogukogus,asukoht, link

How can I fix this problem?

Answer 1

Thanks for your answers, this is really silly but when removing kogukogus from group by and result as this:

SELECT product_id, product_reference AS kood,product_name AS nimetus,product_quantity AS nr, pl.link_rewrite,psa.quantity as kogukogus,group_concat(ps.product_supplier_reference) as supp_ref, p.location AS asukoht,
         CONCAT(c.link_rewrite,'/',p.id_product,'-',pl.link_rewrite,'.html') link
        FROM ps_order_detail o
        LEFT JOIN ps_product_lang pl on o.product_id = pl.id_product
        LEFT JOIN ps_product p on p.id_product = pl.id_product
        LEFT JOIN ps_stock_available psa on p.id_product = psa.id_product
        LEFT JOIN ps_category_lang c on c.id_category = p.id_category_default
        LEFT JOIN ps_product_supplier ps on p.id_product = ps.id_product
        WHERE pl.id_lang=2 AND c.id_lang=2 AND id_order= '".$q."'
        GROUP BY product_id, kood,nimetus,nr,pl.link_rewrite,asukoht, link

I got it working somehow. Thanks you @kiks73, Your code didn't work, BUT when I was trying to add group by to you're code, I realized that the problem was with group by. So the bounty goes to you since no one answered anything serious

Answer 2

I think that you have to move 2 of your WHERE conditions to the JOIN conditions. Probably it is enough to move only the condition on the LEFT JOIN. This should be the result:

SELECT DISTINCT product_id, product_reference AS kood,product_name AS nimetus,product_quantity AS nr, pl.link_rewrite,psa.quantity as kogukogus,group_concat(ps.product_supplier_reference) as supp_ref, p.location AS asukoht,
         CONCAT(c.link_rewrite,'/',p.id_product,'-',pl.link_rewrite,'.html') link
        FROM ps_order_detail o 
        JOIN ps_product_lang pl on o.product_id = pl.id_product
        JOIN ps_product p on p.id_product = pl.id_product
        JOIN ps_stock_available psa on p.id_product = psa.id_product
        JOIN ps_category_lang c on c.id_category = p.id_category_default
        JOIN ps_product_supplier ps on p.id_product = ps.id_product
        LEFT JOIN ps_product_attribute pa on p.id_product=pa.id_product
                   AND psa.id_product_attribute = pa.id_product_attribute 

        WHERE pl.id_lang=2 AND c.id_lang=2 AND id_order= '".$q."'

Answer 3

Use SELECT DISTINCT instead simple SELECT in your query if you aren't interesting about particular product attributes for your result