SSS order search not using fulltext index

[dhilditch]

I enabled wp-admin Order search in Settings > SSS > main tab

Then I went to WooCommerce > Orders and searched for something.

With Query Monitor active, if I look at the main query, it used a LIKE operator.

Something must have changed in their order hooks or something to stop my order overrides working to replace LIKE with a proper MATCH (full text index search)

Can you please replicate this issue on your local and code an update so that the order search is correctly replaced by a fulltext search?

[hmkayani]

can you guide me on this, what is the flow of orders query and the potential code files that have the code for SSS orders query.

[dhilditch]

(this ended up involving quite a bit - see towards the end for my discoveriees to help you get this done more quickly)

Option name is override_wpadmin_woocommerce_orders

So search for that in the code.

However - the above is for WooCommerce Legacy Orders storage (orders stored in wp_postmeta).

We don't really need to bother about legacy storage. So ignore that. I provided above for background of how I investigated.

Then the next part is to enable High performance order storage:

With that enabled, if we search again in orders we see the new source of search:

These are 2 similar queries, starting from the same function, the first fetches the orders, the 2nd seems to just be fetching the count, not sure why they do that since the first query will return the count...

Here's a function with some $args we might want to filter - e.g. we could move s into a different query arg called sss and then that would stop the current LIKE stuff from happening.

Ok - so i found the filter you can use for HPOS search:

In the Woo code, there is a filter in HPOS (high performance order system):

            'woocommerce_hpos_generate_where_for_search_filter',
            '',
            $this->search_term,
            $search_filter,
            $this->query
        );```

So - 

1. Enable HPOS
2. Add a filter onto woocommerce_hpos_generate_where_for_search_filter
3. Add a breakpoint inside your filter
4. Perform a search
5. Examine the search filter, and replace LIKE operations with MATCH operations

You will also need to adjust the table build to add a full text index to the underlying tables.

wp_wc_orders needs a fulltext index on billing_email, transaction_id
wp_wc_orders_meta needs a fulltext index on meta_value
wp_woocommerce_order_items needs a fulltext index on order_item_name 

And you need to reconstruct the query but using MATCH (cols...) AGAINST('+{search_string}*')

SELECT wp_wc_orders.id FROM wp_wc_orders WHERE 1=1 AND (wp_wc_orders.status IN ('wc-pending','wc-processing','wc-on-hold','wc-completed','wc-cancelled','wc-refunded','wc-failed')) AND (wp_wc_orders.type = 'shop_order') AND ( ( wp_wc_orders.transaction_id LIKE '%dave%' OR wp_wc_orders.billing_email LIKE 'dave%' OR wp_wc_orders.id IN ( SELECT search_query_meta.order_id FROM wp_wc_orders_meta as search_query_meta WHERE search_query_meta.meta_key IN ( '_billing_address_index','_shipping_address_index' ) AND search_query_meta.meta_value LIKE '%dave%' GROUP BY search_query_meta.order_id ) OR wp_wc_orders.id in ( SELECT order_id FROM wp_woocommerce_order_items search_query_items WHERE search_query_items.order_item_name LIKE '%dave%' ) ) ) GROUP BY wp_wc_orders.id ORDER BY wp_wc_orders.date_created_gmt DESC LIMIT 0, 20

[hmkayani]

@dhilditch issue I am facing is that order search is not triggering "woo_order_search_fix" because

• "REST_REQUEST" is not defined
• also if I ignore "REST_REQUEST" and return "true" from the function "is_wc_orders_api_request()" the wp_query variable inside "woo_order_search_fix()" is set but values are either null or empty.