How to add WooCommerce SKU to search query?

Question

I have issue adding the meta_value in search query for the WooCommerce SKU. By default, the search by SKU only work on the admin.

I would like to make the frontend search accepting SKU in search.

Note : SKU are not in the product title. So I need to create a custom query.

 function SearchFilter($query) {

  if ($query->is_search) {

    $meta_query_args = array(
      'relation' => 'OR',
      array(
        'key' => '_sku',
        'value' => $query->query_vars['s'],
        'compare' => '=',
      )
    );

    $query->set('post_type', array('post','page', 'product'));
    $query->set('post_status', array('publish'));
    $query->set('meta_query', $meta_query_args);
  }
  
  return $query;

}
add_filter('pre_get_posts','SearchFilter');

The problem : When I place this code and I print the current SQL request, it gives me something like this.

    SELECT SQL_CALC_FOUND_ROWS  bhd_posts.ID FROM bhd_posts  INNER JOIN bhd_postmeta ON ( bhd_posts.ID = bhd_postmeta.post_id ) WHERE 1=1  AND (((bhd_posts.post_title LIKE '%96242-20VH%') OR (bhd_posts.post_excerpt LIKE '%96242-20VH%') OR (bhd_posts.post_content LIKE '%96242-20VH%')))  AND (bhd_posts.post_password = '')  AND ( 
      ( bhd_postmeta.meta_key = '_sku' AND bhd_postmeta.meta_value = '96242-20VH' )
    ) AND bhd_posts.post_type IN ('post', 'page', 'product') AND ((bhd_posts.post_status = 'publish')) GROUP BY bhd_posts.ID ORDER BY bhd_posts.post_title LIKE '%96242-20VH%' DESC, bhd_posts.post_date DESC LIMIT 0, 10

As you can see, it tries to fetch for the “classic part” in the table x_posts for post_title OR post_excerpt OR post_content AND it must have a meta_value of my SKU.

As told above, my product titles do not have the sku in them.

Goal : Having to search in titles, excerpt, content or in meta_value OR search exclusivly with the meta_value.

0
Patrice Poliquin 3 months 0 Answers 54 views 0

Leave an answer