SQL Query to list the products, their stock, and the final price

[monarche]

Hello,

Everything is in the title. I try to create a module that needs to list the catalog products from a new custom table.

And frankly, I really stuck. Always the products result have a different price that PrestaShop catalog. Yet later I apply the reduction and still nothing. (sorry for my english, google).

 
Here is the query:

SELECT pcf.*, pl.`description` , pl.`description_short` , pl.`name`, ps.`quantity`, pa.`id_product_attribute` ,(p.`price` * ( ( 100 + ( t.`rate` ) ) /100 )) 
AS orderprice , ag.`id_attribute_group` , agl.`name` AS group_name, al.`name` AS attribute_name, a.`id_attribute`
FROM `'._DB_PREFIX_.'custom_freestyle` pc
LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON ( pcf.`free_productid` = pl.`id_product` AND pl.`id_lang` =2 )
LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (pcf.`free_productid` = pa.`id_product` AND pcf.`free_productref` = pa.`id_product_attribute`)
LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = pcf.`free_productid`
LEFT JOIN `'._DB_PREFIX_.'stock_available` ps ON (ps.`id_product` = pcf.`free_productid` AND ps.`id_product_attribute` = pcf.`free_productref`)
LEFT JOIN `'._DB_PREFIX_.'product_attribute_combination` pac ON pcf.`free_productref` = pac.`id_product_attribute`
LEFT JOIN `'._DB_PREFIX_.'attribute` a ON a.`id_attribute` = pac.`id_attribute`
LEFT JOIN `'._DB_PREFIX_.'attribute_group` ag ON ag.`id_attribute_group` = a.`id_attribute_group`
LEFT JOIN `'._DB_PREFIX_.'attribute_lang` al ON ( a.`id_attribute` = al.`id_attribute` AND al.`id_lang` =2 )
LEFT JOIN `'._DB_PREFIX_.'attribute_group_lang` agl ON ( ag.`id_attribute_group` = agl.`id_attribute_group` AND agl.`id_lang` =2 ) 
LEFT JOIN `ps_tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`	AND tr.`id_country` = 1	AND tr.`id_state` = 0)
LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)
WHERE pcf.`free_active`=1
GROUP BY pcf.`free_ref` 

really thank you.
cordially

[PascalVG]

The price is nowadays stored in the table ps_product_shop    (This changed since PrestaShop introduced the multishop functionality. The ps_product-> price field is not used/reliable anymore.

 

Hope this helps,

pascal

[monarche]

thank you for your help ^^