[Question] How to check which customer ordered specific item

[renjii]

Hi 

Is there a way we can pull out and know that how many customer has ordered that item

 

for example

 

Product ID 81 ItemABC 

i want to know how many customer ordered this item with their name 

 

is there a module or plug in to do it or by default can prestashop do this ? i cant seems to find the option

 

thank you in advance !

[tuk66]

You can create a SQL query to use in SQL Manager.

[renjii]

Hi M4

Do you have an example how to do the query or do you know any module that can do this ?

[tuk66]

I don't have any SQL query, but it is not so tough to create one in 15 minutes.

[PascalVG]

Add the following SQL in Advanced Parameters -> SQL manager (Add new SQL)

 

SELECT p.id_product, 

    pl.name as `product name`, 

    (SELECT COUNT(DISTINCT ot.`id_customer`)

          FROM `ps_order_detail` odt 

          INNER JOIN `ps_orders` ot ON (odt.`id_order` = ot.`id_order` )

          WHERE p.`id_product` = odt.`product_id`) 

        as `nmbr_cust_have_product`,

    c.id_customer, 

    CONCAT_WS(" ", c.`firstname`, c.`lastname`) AS `cust_name`, c.email, 

    c.birthday, c.newsletter

FROM ps_product p 

    INNER JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product`) 

    INNER JOIN `ps_order_detail` od ON (p.`id_product` = od.`product_id`) 

    INNER JOIN `ps_orders` o ON (od.`id_order` = o.`id_order`) 

    INNER JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)

WHERE pl.id_lang = '1' 

GROUP BY p.id_product, c.id_customer

ORDER BY p.id_product, cust_name

 

Result:

 

If you want to have some more details about the orders that the customer ordered with that product in it, you can use:

 

SELECT p.id_product, 

    pl.name as `product name`, 

    (SELECT COUNT(DISTINCT ott.`id_customer`)

          FROM `ps_order_detail` odtt 

          INNER JOIN `ps_orders` ott ON (odtt.`id_order` = ott.`id_order` )

          WHERE p.`id_product` = odtt.`product_id`) 

        as `nmbr_cust_have_product`,

    c.id_customer, 

    CONCAT_WS(" ", c.`firstname`, c.`lastname`) AS `cust_name`, c.email, 

    c.birthday, c.newsletter,

    (SELECT COUNT(odtt.`id_order`)

          FROM `ps_order_detail` odtt 

          INNER JOIN `ps_orders` ott ON (odtt.`id_order` = ott.`id_order` )

          WHERE p.`id_product` = odtt.`product_id` && ott.id_customer = c.id_customer ) 

        as `orders`,

    (SELECT group_concat(odt.`id_order` separator ", ")

          FROM `ps_order_detail` odt 

          INNER JOIN `ps_orders` ot ON (odt.`id_order` = ot.`id_order` )

          WHERE p.`id_product` = odt.`product_id` && ot.id_customer = c.id_customer ) 

        as `order IDs` 

FROM ps_product p 

    INNER JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product`) 

    INNER JOIN `ps_order_detail` od ON (p.`id_product` = od.`product_id`) 

    INNER JOIN `ps_orders` o ON (od.`id_order` = o.`id_order`) 

    INNER JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)

WHERE pl.id_lang = '1' 

GROUP BY p.id_product, c.id_customer

ORDER BY p.id_product, cust_name

 

Result:

 

Hope this helps,

pascal

 

 

 

P.S. If you use a specific language, change the language ID accordingly in the SQL's above on this line:

 

WHERE pl.id_lang = '1' 

Edited May 12, 2015 by PascalVG
If other language, change language ID (see edit history)

[Tung at RockPOS.com]

Perfect SQL ^^