Jump to content

Mysql query product with and with out attribute

Recommended Posts


I'm new in prestashop and msql and i am working on a query that fits my needs except for one thing. The result of the query show's only one product with out attributes my database(test environment) contains more than one product with out attributes. I am working  a few days to find a solution but so far nothing found yet. So is there any one with a suggestion to solve this problem. This is the Query.

SELECT  CONCAT(pl.name, ': ', CASE WHEN al.name is not null THEN GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") ELSE "" END) as Product,
        p.reference AS Referentie,
        p.ean13 AS Barcode,
        s.quantity AS Voorraad,
        p.price AS Verkooppr,
        su.name AS Leverancier,
        ps.product_supplier_price_te AS Inkooppr,
        ps.product_supplier_reference AS Artikelnr,
        `ps_product_attribute`.`ean13` AS Barcode,
         cl.`name`  AS `name_category`
From     ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al ON (al.id_attribute = pac.id_attribute)
LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product AND (s.id_product_attribute = pa.id_product_attribute OR CASE WHEN pa.id_product_attribute is null THEN s.id_product_attribute = 0 END))
LEFT JOIN ps_supplier su ON (su.id_supplier = p.id_supplier)
LEFT JOIN ps_product_supplier ps ON (ps.id_product = p.id_product AND (ps.id_product_attribute = pa.id_product_attribute OR CASE WHEN pa.id_product_attribute is null THEN ps.id_product_attribute = 0 END))
LEFT JOIN ps_product_attribute ON (`ps_product_attribute`.`id_product` = p.id_product)
JOIN `ps_product_shop` sa ON (p.`id_product` = sa.`id_product` AND sa.id_shop = 1)
LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1)
WHERE pl.id_lang = 1
AND p.active = 1
AND         p.id_shop_default = 1
GROUP BY     pac.id_product_attribute
ORDER BY s.quantity ASC


Thanks for reading.


Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More