SQL to obtain Stock Quantity

[kenkomuri]

Hi,

 

I would like to know if somebody got a SQL statement to obtain the stock quantity, I got the following but I can not get the description and some products are not in the list:

SELECT distinct pa.reference Referencia, pp.reference Referencia_2, 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_stock ps ON (p.id_product_attribute = ps.id_product_attribute) 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 Producto, Referencia, Referencia_2

The fields I need is the reference, name, description, stock available. I am not using the advanced stock management but if you have the query of the basic and advanced mode is better .

 

I hope you can help me.

 

Fernando

[tuk66]

PrestaShop version?

[kenkomuri]

1.5.0.17

[tuk66]

It works for me. Just the description is added:

 

SELECT distinct pa.reference Referencia, pp.reference Referencia_2, pl.name Producto, p.quantity Cantidad, pl.description_short
FROM pr_stock_available p
LEFT JOIN pr_product pp ON (p.id_product = pp.id_product)
LEFT JOIN pr_stock ps ON (p.id_product_attribute = ps.id_product_attribute)
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 Producto, Referencia, Referencia_2

[kenkomuri]

It works for me. Just the description is added:

 

SELECT distinct pa.reference Referencia, pp.reference Referencia_2, pl.name Producto, p.quantity Cantidad, pl.description_short

FROM pr_stock_available p

LEFT JOIN pr_product pp ON (p.id_product = pp.id_product)

LEFT JOIN pr_stock ps ON (p.id_product_attribute = ps.id_product_attribute)

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 Producto, Referencia, Referencia_2

 

Thank you so much, I think I dont specify my requirement very well. What I need is the combination name instead of the description of the product, the field pl.name gives the name of the product in general, NOT the combinations.

 

Thank you so much again

[tuk66]

Try this one:

 

SELECT distinct pa.reference Referencia, pp.reference Referencia_2, pl.name Producto, p.quantity Cantidad, al.name Combination_name
FROM pr_stock_available p
LEFT JOIN pr_product pp ON (p.id_product = pp.id_product)
LEFT JOIN pr_stock ps ON (p.id_product_attribute = ps.id_product_attribute)
LEFT JOIN pr_product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = 4)
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 AND al.id_lang = 4)
order by Producto, Referencia, Referencia_2

[kenkomuri]

Try this one:

 

SELECT distinct pa.reference Referencia, pp.reference Referencia_2, pl.name Producto, p.quantity Cantidad, al.name Combination_name

FROM pr_stock_available p

LEFT JOIN pr_product pp ON (p.id_product = pp.id_product)

LEFT JOIN pr_stock ps ON (p.id_product_attribute = ps.id_product_attribute)

LEFT JOIN pr_product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = 4)

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 AND al.id_lang = 4)

order by Producto, Referencia, Referencia_2

 

Why 4 and not 7 in the id_lang? Moreover I cant run it, it shows me an error. The field name is in the table pr_attribute_lang?

Edited October 4, 2013 by kenkomuri (see edit history)

[tuk66]

Of course, it should be 7. I test it with 4.

[kenkomuri]

But I get an error, dont you? I think it is because in my case the field name in the al table have more than 1 combination.

Edited October 4, 2013 by kenkomuri (see edit history)

[tuk66]

An error is not the same as "more combinations". There is a difference between a syntax error in SQL query and an unwanted result.

[kenkomuri]

An error is not the same as "more combinations". There is a difference between a syntax error in SQL query and an unwanted result.

 

Thanks for your advice. Do you have the correct query?

[kenkomuri]

Does anyone could solve this?

[kenkomuri]

What I am looking for is not exactly description, is the field that appears on the Combination section, named "Attributes" what is the total attributes for every combination. Any idea?

 

Id like to get the reference, product, attributes, stock quantity fields.

[ropencho]

Hi!!! I Try to export de DB with the SWL Manager but when i run de querie the quantity field is 0 and dont show the real stock. I searched on MYSQL but i dont know where Prestashop is saving the quantitys.

 

I used this querie

 

SELECT p.id_product, p.active, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as categories, p.price, p.id_tax_rules_group, p.wholesale_price, p.reference, p.supplier_reference, p.id_supplier, p.id_manufacturer, p.upc, p.ecotax, p.weight, p.quantity, p.out_of_stock, pl.description_short, pl.description, pl.meta_title, pl.meta_keywords, pl.meta_description, pl.link_rewrite, pl.available_now, pl.available_later, p.available_for_order, p.date_add, p.show_price, p.online_only, p.condition, p.id_shop_default
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
AND p.id_shop_default = 1 AND c.id_shop_default = 1
GROUP BY p.id_product

 

I Have PrestaShop™ 1.5.4.1