SQL trouble

[Jonathan75004]

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:

{
SELECT *
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.

[musicmaster]

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.

[Jonathan75004]

Good tip musicmaster. I have excluded orders cancelled and never paid. And it worked.

 

Now i'll compare the numbers with my orders (paper) for be sure.

Thank you for helpping me!