Jump to content

SQL trouble


Recommended Posts

Hello everyone,

I introduce myself, Jonathan. I am new to the PrestaShop community and in development.

My problem is in the subject of BDD sql.

My task is simple verbally: "Show the number of sales of each product in my shop."
So I exported the products to the list sql manager.


SELECT SQL_CALC_FOUND_ROWS a.`id_product`, b.name as name, `reference`, supplier_reference, a.price as price, sa.active as active, cl.name` name_category`, sav.`quantity` have sav_quantity, sa.` active`, IF (sav.`quantity` <= 0, 1, 0) badge_danger

FROM `has ps_product`

LEFT JOIN `ps_product_lang` b ON (= b.`id_product` a.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = 1) LEFT JOIN` ps_image` i ON (i.`id_product` = a.`id_product`)

LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` a.`id_product` = = 0 AND sav.`id_product_attribute`

AND sav.id_shop = 1 AND sav.id_shop_group = 0) JOIN `ps_product_shop` its ON (= a.`id_product` sa.`id_product` AND sa.id_shop = a.id_shop_default)

LEFT JOIN `ps_category_lang` cl ON (= sa.`id_category_default` cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default)

LEFT JOIN `ps_shop` shop ON (= shop.id_shop a.id_shop_default)

LEFT JOIN `ps_image_shop` image_shop ON (= image_shop.`id_image` i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = a.id_shop_default)

LEFT JOIN `ps_product_download` pd ON (= pd.`id_product` a.`id_product`)

WHERE 1 ORDER BY GROUP BY sa.id_product a.`id_product` ASC


Until then, no worries!

I would now add the number of sales by product.
So I run the sql command below, to see what would come out before the partner with the above command:

FROM ps_product_sale


However columns "quantity" and "sale_nbr" out numbers that do not match much to me and are often identical. What keeps me going further.

How can I view the number of sales by products in my product list?
Can you help me please ? I begin to curl :-/

Thank you in advance for your help.

Link to comment
Share on other sites

If you look at the source code of classes\productSale.php you will see that it includes all order detail lines. That is not correct as you should exclude orders that were cancelled or never paid.


So instead you should make a direct query on the ps_order_detail table.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...