Extending WooCommerce admin product search to use custom fields

This post comes as a follow-up to the wp-admin posts search article. In that article, I was mentioning altering that code to be used for WooCommerce admin product search would be simple!

Turns out, from the comments, that it’s not so straight forward with the way WooCommerce handles search in the latest versions so I found an alternate way to do that.

The objective here is to enable users to search by a meta value, set for products or product variations.

Why the previous version doesn’t work

That’s pretty simple to spot once you look at the way things run. WooCommerce uses a Data Store for its post types ( products, orders, etc ) and the search is also done using a custom function. In this case it’s called ‘search_products’ and it does a custom query which basically returns an array of ids to be used for the results. The query looks like this:

$search_results = $wpdb->get_results(
   // phpcs:disable
   $wpdb->prepare(
      "SELECT DISTINCT posts.ID as product_id, posts.post_parent as parent_id FROM {$wpdb->posts} posts
      LEFT JOIN {$wpdb->postmeta} postmeta ON posts.ID = postmeta.post_id
      $type_join
      WHERE (
         posts.post_title LIKE %s
         OR posts.post_excerpt LIKE %s
         OR posts.post_content LIKE %s
         OR (
            postmeta.meta_key = '_sku' AND postmeta.meta_value LIKE %s
         )
      )
      AND posts.post_type IN ('" . implode( "','", $post_types ) . "')
      $status_where
      $type_where
      ORDER BY posts.post_parent ASC, posts.post_title ASC",
      $like_term,
      $like_term,
      $like_term,
      $like_term
   )
   // phpcs:enable
);

In this query, WooCommerce searches for the search term in the product title, description and the SKU.

The SKU search is interesting as it’s also stored as a meta. We can copy that to use the same structure.

The solution

A quick solution to this is to alter the query later and append the results to the “post__in” variable of the query args. Place this code in your theme’s functions.php or in a custom plugin.

Update: there was a mistake in the initial version, no new array was needed on line 25.

Update #2: Ray asked in the comments and I added a second example which includes the option to search for multiple, comma separated, SKUs at the same time.

See the gist on github.

This will work and add the results to the admin.

There’s one thing I would look further into. And that’s an attempt to replace the first query done by WooCommerce with a query which also includes the custom meta key we are looking for. This way, you’d end up with one query instead of 2 before the main query.

I don’t expect performance issues though, and this definitely is more update-proof as it doesn’t remove any WooCommerce filters which might change in time.