Search in orders by product name in Prestashop

If you need to check how many and what orders contain a specific product, there is a simple SQL query that can help you. And thanks to Prestashop’s ‘SQL Manager’ menu you can run a query and export a CSV with the results right from the store’s backend.

This works, as far as I tested, on Prestashop 1.5.x, Prestashop 1.6.x and even Thirty Bees.

I’m going to keep this short and simple, so this is the query:

SELECT product_id, product_attribute_id, product_name, GROUP_CONCAT(id_order SEPARATOR ', ') AS orders FROM ps_order_detail WHERE product_name LIKE '%PRODUCT NAME%' group by product_name order by product_id, product_attribute_id

And replace PRODUCT NAME with your product’s name or keyword. Keep the percentage signs.

How to save this and use it when needed?

1. Go to your store’s backend, obviously log in.
2. Go to Advanced Parameters -> SQL Manager
3. Click on the plus sign to add some new interogation
4. Paste the Name (something easy to remember like ‘find orders of product’)
5. And in the SQL Query paste the code above, and modify PRODUCT NAME with the name of one of your products.
6. Save.

An now you can just hit Export and the store will export a CSV file with the results. In the future, just modify the product name and export again. You can even use a keyword like ‘t-shirt’ instead of the full product name if you want to see orders for all your t-shirts.

Some examples

When using this query on a store:

SELECT product_id, product_attribute_id, product_name, GROUP_CONCAT(id_order SEPARATOR ', ') AS orders FROM ps_order_detail WHERE product_name LIKE '%Red Taurus T-shirt with rainbow%' group by product_name order by product_id, product_attribute_id

I got this CSV file:

• 

Now, if I would need more details I could just go in the backend and filter the orders by one of those ID’s and see who ordered and other order details.

And when using this query on the same store:

SELECT product_id, product_attribute_id, product_name, GROUP_CONCAT(id_order SEPARATOR ', ') AS orders FROM ps_order_detail WHERE product_name LIKE '%T-shirt%' group by product_name order by product_id, product_attribute_id

I got this CSV file:

• 

That’s it!

So there you have it! A nice and fast way to find order ids by the product name. No additional plugins or add-ons no nothing. I hope this is useful to someone out there!