SQL Manager query to get stock quantities

[Guest]

Hi

 

I have some problems creating a SQL query to get all products and their current stock quantity. I have managed to get the complete list of all products. The stock level is also right for products with attributes but I can not get the stock level for products without any attributes right. 

 

The minimal version of my query is:

SELECT
p.id_product,  pl.name, IF(pa.ean13, pa.ean13, p.ean13) AS EAN13, psa.quantity
FROM ps_product p
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available psa ON (pa.id_product_attribute = psa.id_product_attribute)
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) 
WHERE pl.id_lang = 1
ORDER BY p.id_product

This correctly lists all products and correct stock levels for products with attributes. However, I think because of the second JOIN, it looses the quantity in ps_stock_available for products without attributes.

 

Can anyone help correcting the query so I can see the stock level also for products without attributes?

[hakeryk2]

Try this one. 

I am using this in one of my module

 

SELECT  CONCAT(pl.name, ': ', CASE WHEN al.name is not null THEN GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") ELSE "" END) as name,
                    s.quantity
            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))
            WHERE pl.id_lang = 1
            AND p.id_shop_default = 1
            GROUP BY pac.id_product_attribute
            ORDER BY s.quantity DESC

Try add after "AND p.id_shop_default = 1" AND p.id_product = and insert id of product that does not have combination to check if this works correctly.
It is kinda heavy query because a bunch of joins but You already have proper attributes names to have better information about every one in store.

Edit: You don't have to check this - it works.

Edited August 21, 2017 by hakeryk2 (see edit history)

[Guest]

Thanks for the reply.

 

It seems that my 1.5.x version of Prestashop does not support adding AND or OR inside the JOIN statements. I had this problem before. 

 

So I am sure your query is fine but if using these AND/OR in the JOIN is needed I have to wait until I manage to update Prestashop.

[hakeryk2]

Uhm AND and OR are not related to Your prestashop version but mysql. If You will paste this into PhpMyAdmin in SQL section then You will see correct results but if You want to use this in prestashop SQL Menager that I think it will be to complex for presta. I just tested it on 1.6 and it doesn't work so it will be kinda pain in the ass. Give up the Presta solution, login directly into PhpMyAdmin and You have option to save your query as well.

Are You receving any error?

Edited August 21, 2017 by hakeryk2 (see edit history)

[Scully]

I also cannot imagine MySQL not to resolve AND and OR in joins. It's such a basic functionality. But an error message would help in any case.