Since 3.0, Order Search seems to miss some orders

ScottCodes
(@scottlush)

5 years, 11 months ago

Hi,

Since we updated to WooCommerce 3.0 then 3.0.1 our WooCommerce >> Orders >> Search Orders results misses some orders.

For example, if we search in WooCommerce >> Orders for “John Doe” it may show 1 order in the results. But if we click on John Doe’s username and go to his “Customer Data Tab” under “Order History” it may show 2 or more orders.

Also on his Users >> “Order History” tab all orders will have the incorrect date of January 1, 1970 12:00 am (which I think is the 1st date for UNIX) – even though if you view the actual orders they have the real date like April 5, 2017 2:00pm.

Has anyone else encountered this? Can you suggest a solution please?

Viewing 7 replies - 1 through 7 (of 7 total)

Plugin Author Mike Jolley
(@mikejolley)

5 years, 11 months ago

It may not be apparent, but doing an SQL query where you join “John” to “Doe” is not performant and cannot scale.

In 3.0 we added a new search index which actually saves the string “John Doe” to a new field which is then searchable without so much performance impact. The downside is this cannot be applied retrospectively to past orders. So when doing these types of search, only new and updated orders will be shown.

Thread Starter ScottCodes
(@scottlush)

5 years, 11 months ago

Hi Mike,

Thanks for explaining.

Apologies if I misunderstood your response, but is there no way to search for all of John Doe’s past orders (including both pre- and post- WooCommerce 3.0) through WooCommerce >> Orders?

Or, is there a way to cause WooCommerce to reindex all past orders for the new search index so that we can then find by search all of say John Doe’s past orders?

The reason is that our business runs on repeat sales, maybe like some other WooCommerce sites, so we greatly value being able to search by customer name and find all orders.

Again, sorry if I misunderstood your reply.

Scott

Plugin Author Mike Jolley
(@mikejolley)

5 years, 11 months ago

You can search those past orders with surname only, but the first last combination won’t work with old orders which have not been indexed.
Thread Starter ScottCodes
(@scottlush)

5 years, 11 months ago
Mike,

Is the searchable full name like “John Doe” that you referred to in WooCommerce 3.0 now placed in the “display_name” column in “wp_users”?

And is that column built from the wp_postmeta table of the columns _billing_first_name + “ “ + _billing_last_name?

This can help us write maybe a SQL script that retroactively scans past orders and updates their display_name field.

Maybe that will allow us to scan by name like “John Doe” for all past orders, both pre- and post- WooCommerce 3.0
- This reply was modified 5 years, 11 months ago by ScottCodes.
- This reply was modified 5 years, 11 months ago by ScottCodes.
indextwo
(@indextwo)

5 years, 10 months ago

I had the exact same issue with a number of client sites, and didn’t fully understand what was happening until I found this thread. As we had tens of thousands of orders to process, I built a plugin to do just that:

https://wordpress.org/plugins/order-search-repair-for-woocommerce/

Order Search Repair will scan all older orders that do not have a searchable index and either update them live or output the SQL queries so you can update the database directly yourself.

The smallest site I used it on only had about 400 orders and it took a few seconds. The largest site had over 50,000 orders and it took about 25-30 minutes (not exactly sure how long, as I went and had lunch while it was running). In total, I used the plugin to process 89,743 orders over 5 sites, and now the searching for all orders works like a charm.
JulieMarie
(@juliemarie)

5 years, 10 months ago
Thanks for the plugin.
I used it to process all my orders however the woocommerce order history in the Customer Data tab still reads January 1, 1970 12:00 am for all orders
Are there any ideas on how to resolve this?
So far the only solution I have found has been to downgrade to woocommerce 2.6.14 however this solution is no longer compatible with the other woocommerce plugins I’m running.
- This reply was modified 5 years, 10 months ago by JulieMarie.
JulieMarie
(@juliemarie)

5 years, 10 months ago

Some more information I thought might be pertinent…Searching by order and/or subscription still yields no results. I’ve tried searching by first name, last name, order number and subscription number…each time I get nothing. Any more information I can requested in resolving this issue will be provided.