How to add WooCommerce SKU to search query?

by

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.

1 Answer
1

If you are using wordpress search you can add this code to make it work

function search_by_sku( $search, &$query_vars ) {
    global $wpdb;
    if(isset($query_vars->query['s']) && !empty($query_vars->query['s'])){
        $args = array(
            'posts_per_page'  => -1,
            'post_type'       => 'product',
            'meta_query' => array(
                array(
                    'key' => '_sku',
                    'value' => $query_vars->query['s'],
                    'compare' => 'LIKE'
                )
            )
        );
        $posts = get_posts($args);
        if(empty($posts)) return $search;
        $get_post_ids = array();
        foreach($posts as $post){
            $get_post_ids[] = $post->ID;
        }
        if(sizeof( $get_post_ids ) > 0 ) {
                $search = str_replace( 'AND (((', "AND ((({$wpdb->posts}.ID IN (" . implode( ',', $get_post_ids ) . ")) OR (", $search);
        }
    }
    return $search;
    
}
    add_filter( 'posts_search', 'search_by_sku', 999, 2 );

Leave a Comment