SQL Statement to Extract Stock Available by Product

[kenkomuri]

Hi,

 

I would like to run a SQL statement to obtain the product with the available stock. I try to run this but does not work:

SELECT prod.reference Referencia, prodat.reference RefAtrib, prodlang.name Producto, atlang.name Atributo, prod.price Precio, stock.quantity Cantidad
FROM
pr_product prod
LEFT JOIN pr_stock_available stock ON (prod.id_product = stock.id_product)
LEFT JOIN pr_product_attribute prodat ON (prod.id_product = prodat.id_product)
LEFT JOIN pr_product_lang prodlang ON (prod.id_product = prodlang.id_product),
pr_product_attribute
LEFT JOIN pr_product_attribute_combination prodatcomb ON (prodat.id_product_attribute = prodatcomb.id_product_attribute),
pr_product_lang
LEFT JOIN pr_attribute_lang atlang ON (prodlang.id_lang = atlang.id_lang),
pr_product_attribute_combination
LEFT JOIN pr_attribute_lang atlang ON (prodatcomb.id_attribute = atlang.id_attribute)
WHERE
prodlang.id_lang = 7

I also have this statement that WORKS but don't show the products without attributes (that are not on the attributes tables, in other words, without combination):

SELECT distinct pp.reference RefProd, pa.reference RefAtrib, pl.name Producto, p.quantity Cantidad
FROM pr_stock_available p
LEFT JOIN pr_product pp ON (p.id_product = pp.id_product)
LEFT JOIN pr_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN pr_product_attribute_combination pac ON (p.id_product_attribute = pac.id_product_attribute)
LEFT JOIN pr_product_attribute pa ON (p.id_product_attribute = pa.id_product_attribute)
LEFT JOIN pr_attribute_lang al ON (pac.id_attribute = al.id_attribute)
WHERE pl.id_lang = 7 AND al.id_lang = 7
ORDER BY RefAtrib, RefProd, Producto

Any idea would be helpful.

 

I am using prestashop 1.5.0.17

[tuk66]

Look at Product::sqlStock method and all its use.

[kenkomuri]

Look at Product::sqlStock method and all its use.

Whats that method?

[tuk66]

Create JOIN query with 'stock_available' table

[kenkomuri]

Create JOIN query with 'stock_available' table

 

It did not work also but thanks