Search products in orders

[Neo_odessa]

How to create query to find products in table order_details and paste input with search to back office.

Task is:

Find products by reference  in orders with certain statuses

As example I need find product with reference "demo_1" in orders with statuses PS_OS_PREPARATION

and show all orders which contain this product

[rocky]

Try the following query:

SELECT order_detail.`id_order`
FROM (SELECT `id_order`, `id_order_state`
      FROM (SELECT *
            FROM `ps_order_history`
            ORDER BY `date_add` DESC) AS reverse_order_history
      GROUP BY `id_order`) grouped_order_history
LEFT JOIN `ps_order_detail` order_detail ON grouped_order_history.`id_order` = order_detail.`id_order`
WHERE `id_order_state` = 3 AND `product_reference` = "demo_1"

Change ps_ to your database prefix if needed. This query gets the ps_order_history table in reverse order by date and then groups it by id_order to get only the newest order state for each order, then it joins with the ps_order_detail to get the product information, then it filters by orders whose newest order state is "Processing in progress" and contain a product with the reference "demo_1".

[musicmaster]

Prestools has a function for that if you prefer working with an interface.

[Neo_odessa]

Prestools has a function for that if you prefer working with an interface.

I`ve installed Prestools trying find product in orders in tab "Product edit" But there I cant choose orders And can`t see orders numbers. Can you suggest how to do that threw Prestools 

Thanks

[Neo_odessa]

Try the following query:

SELECT order_detail.`id_order`
FROM (SELECT `id_order`, `id_order_state`
      FROM (SELECT *
            FROM `ps_order_history`
            ORDER BY `date_add` DESC) AS reverse_order_history
      GROUP BY `id_order`) grouped_order_history
LEFT JOIN `ps_order_detail` order_detail ON grouped_order_history.`id_order` = order_detail.`id_order`
WHERE `id_order_state` = 3 AND `product_reference` = "demo_1"

Change ps_ to your database prefix if needed. This query gets the ps_order_history table in reverse order by date and then groups it by id_order to get only the newest order state for each order, then it joins with the ps_order_detail to get the product information, then it filters by orders whose newest order state is "Processing in progress" and contain a product with the reference "demo_1".

I`ve tried add your suggested query to SQL manager but it doesnt work I`ve obtain an error: Undefined "checkedFrom" error

[rocky]

Strange. It works fine in phpMyAdmin. I guess the PrestaShop SQL Manager doesn't support SELECT queries that complicated.

[Neo_odessa]

Strange. It works fine in phpMyAdmin. I guess the PrestaShop SQL Manager doesn't support SELECT queries that complicated.

You right I`ve tried threw phpMyAdmin it works Great!

Any chance do query in SQL manager? I need somehow save this query to SQL manager. Or maybe I can do form with input in backoffice?

[rocky]

I've had a closer look at the Orders page and it seems you can already filter by "Processing in progress", so all you really need is an additional filter where you can enter a product reference. I think it's too much work for me to do for free though.

 

We'll see if musicmaster can explain how to do it using Prestatools. If not, I can try to write an override for AdminOrdersController.php that will allow you to filter by product reference.

[Neo_odessa]

I've had a closer look at the Orders page and it seems you can already filter by "Processing in progress", so all you really need is an additional filter where you can enter a product reference. I think it's too much work for me to do for free though.

 

We'll see if musicmaster can explain how to do it using Prestatools. If not, I can try to write an override for AdminOrdersController.php that will allow you to filter by product reference.

I will wait for musicmaster answer but I think  Prestatools don`t have this filter I`m discovered Prestatools and can`t find this 

 

It will be perfect if you can help with additional filter in AdminOrdersController.php. If it much work you can contact me in private message I`m ready to pay for this help ))

[musicmaster]

See the attached image.

 

You can add your search text after "Find". Make sure that you select product name in the box below it.

[Neo_odessa]

See the attached image.

 

You can add your search text after "Find". Make sure that you select product name in the box below it.

I see screanshot but I dont have this option. When I over mouse to "Order Edit" I dont have dropdown menu with  "Order Search" you showing on screanshot

[musicmaster]

Do you have the latest version?

[Neo_odessa]

Do you have the latest version?

I`ve installed from this link 

https://www.prestashop.com/forums/index.php?app=core&module=attach&section=attach&attach_id=151011

[Neo_odessa]

I put url directly to /order-search.php and now I can find products in orders in Prestools

But I cant filter orders by statuses. It possible?

[musicmaster]

I could add that in the next version. Do you have suggestions how to fit that in the interface?

[Neo_odessa]

I could add that in the next version. Do you have suggestions how to fit that in the interface?

First I`ve tried to find order state in select fields it have "order_id"... etc. there will be great add "order_state"

[musicmaster]

Please try the attached update

order-search.php

[Neo_odessa]

Please try the attached update

Thanks awesome script now I can filter by statuses.