WC 3.0 Orders Search

Running this on all orders (some stores could have thousands) did not sound like a great idea – it would certainly time out on many, thats why the updater does not run this.

Whilst they will be missing the indexes, standard searches on things like surname should still function on those orders.

$search_fields = array_map( 'wc_clean', apply_filters( 'woocommerce_shop_order_search_fields', array(
			'_billing_address_index',
			'_shipping_address_index',
			'_billing_last_name',
			'_billing_email',
		) ) );

^ Those fields get searched, and you can filter to add more if needed.

Hey Mike. I’m aware of the filters. The issue is that in real life our customer service department receive many calls where customers don’t have or don’t know the email they use to place the orders neither the order number. They just know their name, address or phone number.

When customer service try to type the complete name and last name combination obviously is not found. If they look by billing_last_name with 93000 orders the results bring many pages or results. So adding extra postmeta with a filter won’t help us to solve this and actually may affect the performance.
In the past versions i modified the search terms to search for the concatenation of name and last name this way:

SELECT DISTINCT p1.post_id
FROM {$wpdb->postmeta} p1
INNER JOIN {$wpdb->postmeta} p2 ON p1.post_id = p2.post_id
WHERE
( p1.meta_key = '_billing_first_name' AND p2.meta_key = '_billing_last_name' AND CONCAT(p1.meta_value, ' ', p2.meta_value) LIKE '%%%s%%' )
OR
( p1.meta_key = '_shipping_first_name' AND p2.meta_key = '_shipping_last_name' AND CONCAT(p1.meta_value, ' ', p2.meta_value) LIKE '%%%s%%' )
",

That’s why i really like this new 2 postmeta fields that concatenate all fields for faster search. The ideal is to be able to run this function that update the postmeta even if it does it with a limit number of order at a time or another option is to run a mysql query that do the same concatenation directly in the db.

What about making a new index along these lines:

insert into wp_postmeta( post_id, meta_key, meta_value )
select pm1.post_id, '_test_key', CONCAT( pm1.meta_value, ' ', pm2.meta_value )
FROM wp_postmeta pm1 INNER JOIN wp_postmeta pm2 ON pm1.post_id = pm2.post_id
WHERE pm1.meta_key = '_billing_first_name' AND pm2.meta_key = '_billing_last_name'
AND pm1.meta_value != '' AND pm2.meta_value != ''

Mike.
Thanks for the query. This works great. Now we have the ability to search all orders prior to WC 3.0 by for first and last name concatenated.

This is the whole solution:
Run this query in the db:

insert into wp_postmeta( post_id, meta_key, meta_value )
select pm1.post_id, '_customer_full_name', CONCAT( pm1.meta_value, ' ', pm2.meta_value )
FROM wp_postmeta pm1 INNER JOIN wp_postmeta pm2 ON pm1.post_id = pm2.post_id
WHERE pm1.meta_key = '_billing_first_name' AND pm2.meta_key = '_billing_last_name'
AND pm1.meta_value != '' AND pm2.meta_value != ''

Then add the filter in functions.php:

/*-------Search Filter------------*/
// define the woocommerce_shop_order_search_fields callback?
function filter_woocommerce_shop_order_search_fields( $search_fields ) {
		array_push( $search_fields, '_customer_full_name' );
		return $search_fields;
}
	      ?-
// add the filter?
add_filter( 'woocommerce_shop_order_search_fields', 'filter_woocommerce_shop_order_search_fields', 10, 1 );

How long did the query take for how many users?

It took around 30 seconds to create 124443 records.

The db is a large MySql hosted on AWS so the performance is good.

Hello!
I am not so good in working with MySql.
Where shall I put the query in the database?

insert into wp_postmeta( post_id, meta_key, meta_value )
select pm1.post_id, ‘_customer_full_name’, CONCAT( pm1.meta_value, ‘ ‘, pm2.meta_value )
FROM wp_postmeta pm1 INNER JOIN wp_postmeta pm2 ON pm1.post_id = pm2.post_id
WHERE pm1.meta_key = ‘_billing_first_name’ AND pm2.meta_key = ‘_billing_last_name’
AND pm1.meta_value != ” AND pm2.meta_value != ”

Shall I replace something in the database or?

My problem is that I cant search for customer or ID:s on the Order Page. I get no matches since I upgraded to latest version of WooCommerce.

Mike