2

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.

12
  • 1
    Why are you using a filter and me_replace_and_with_or for your meta query instead of using the relation parameter set to OR? It's extremely unusual. Keep in mind that attachments don't appear in WordPress search out of the box, they have to be explicitly added to search somehow
    – Tom J Nowell
    Sep 2, 2020 at 15:03
  • I am just trying to figuring out why OR is still returning AND on the query. Sep 2, 2020 at 15:16
  • I can see that from your question, but, the presence of the SQL filter is a major part that cannot be ignored or glossed over, and is super critical. It's directly related to this, and is the most suspicious part of your code, and the most probably cause of your problem. We need to know what it does and why it's there to answer the question.
    – Tom J Nowell
    Sep 2, 2020 at 15:18
  • By default WooCommerce SKU are not in the search query on the frontend (only backend). I want people on the frontend to be able to search by SKU (meta_key _sku). But when I call a "default" search query, it gives the above code. I am now trying to edit the search query to add the meta value in it. Also, I am not interested in using a plugin. Sep 2, 2020 at 15:20
  • I see, you don't need the SQL filter for that though, meta_query already does that. Also keep in mind that plugin recommendations are off-topic here, asking for a plugin would get your question closed as offtopic.
    – Tom J Nowell
    Sep 2, 2020 at 15:22

1 Answer 1

5

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 );
2
  • Thanks for sharing this code. Just to let you know that I tried it but it generates a PHP warning: "Parameter 2 to search_by_sku() expected to be a reference, value given"
    – JulienVan
    Feb 4 at 7:33
  • @JulienVan how you are calling it ?
    – AHSAN KHAN
    2 days ago

Not the answer you're looking for? Browse other questions tagged or ask your own question.