Order Overview Product Filter and search

Hi,

The way search works in the backend of HikaShop (and Joomla and other extensions) is that when you search for something in the input field, it directly add a condition to the main MySQL query loading the elements on the page.
So that means that MySQL have to filter all the entries in the database with your search before then cutting the loaded entries for the pagination.
Also, in MySQL the data is stored in different tables: hikashop_order for the order, hikashop_user for the user, hikashop_address for the address, hikashop_order_product for the products in an order, etc. And since you need to do the cutting of the loaded entries and the search in the same MySQL query, you need to "join" the data of the involved tables together before doing the filtering. And a join means multiplying the data in each table. For example, if you have 10K entries in the hikashop_order table, and each order has roughly 10 products, joining the tables together means creating a temporary table in memory of 10K*10 = 100K entries and then doing the filtering on this temporary table.
Due to this mechanism, we try as much as possible to limit the number of tables to join in that main MySQL query.
It already has to take into account hikashop_order, hikashop_user, users (the user table of Joomla) and twice the hikashop_address so that you can search by email, username, order number, invoice number and billing address and shipping address information. These should be what is most often used on an online shop when handling customers requests on their orders.
And the number of orders can quickly reach hundred of thousands of entries on big online shops.
Supporting the search in products or even categories would be difficult for performance reasons. This would require adding several tables to the main MySQL query, and some of the biggest tables of HikaShop to that. The hikashop_product table can potentially contain a lot of data for each product and thus it could drastically reduce performances, potentially leading to MySQL query errors as the MySQL query wouldn't be able to serve the data in a timely manner.
By having you select the products in a selector, the system can avoid having to search on all the products data, and can directly use the ids of these products in the main MySQL query, reducing the extra load. Making it a separate plugin also prevents this to reduce performances for most of merchants who don't need to search orders based on the products being bought. In most cases, searching with the information of the user is more relevant.

Also, this order product filter plugin is a plugin we initially developed for a user of HikaShop as a custom development. So it was made as a single product selector as per this user's request.
Making it a several products selector could be possible but it raises some questions and potential issues. The simple solution to doing this would be to use the "OR" logic. So the system would load all the orders with either selected products in them. The use of that capability seems quite limited.
You're saying yourself "there are orders with different products in it and It is necessary to filter muliple products at once" so I understand that you're interested in the "AND" logic where the system should load all the orders with all of the selected products in them. This means that for each extra product selected in that selector, the system will have to add two extra joins to the main MySQL query. While it is possible to code that capability, it will add a notable burden on the MySQL server when trying to processing the MySQL query. And the more orders there are in the shop, the more the burden will be heavy.

So, with this message, I explained why the system is the way it is at the moment. As you can read above, there are real reasons why we made it that way. Could you maybe share what is your user case ? Why do you need to be able to search orders by category ? Maybe what you're trying to do can be done in a different way ?

Hi,

What you can do is to create a report via the menu Orders>Reports.
Make it of the display type "Listing" on the period "all", of the type "orders", with the status(es) you want to target, and select the categories to target in the "categories" option.
You can also increase the "limit" setting. This will tell the system how many orders to take into account. The higher the number the higher the load on the MySQL server. I would recommend not going too high and ideally, changing the status of the orders once processed so that the processed orders are ignored by the report. That way, you can just refresh the page of the report once you processed the orders listed in order to get the next batch to process.
The fact that the listing displayed by a report is simpler (less data to load) and doesn't have an input field like the orders listing less tables to join) means that processing the orders with a categories filter is possible. Also, the limit setting allows for a finer "cutting" of the data by MySQL, which also helps.