How do I search title, content OR meta in WP_Query?

Question

I'm trying to add the ability to search for a product SKU on the frontend of our Wordpress+WooCommerce website but I am at a loss (and not exactly a web dev by trade). I've tried quite a few plugins (paid and free) to no avail, and now I am stuck doing this myself.

I have the code:

function woo_custom_search( $query ) { 
    if( ! is_admin() && $query->is_main_query() ) { 
        if ( $query->is_search() ) { 
            $meta_query[] = array(
                    'relation'=> 'OR',
                    array(
                        'key'=> '_sku',
                        'value' => sanitize_text_field( $query->query['s'] ),
                        'compare'=> 'LIKE'
                        )
                    );
            $query->set( 'meta_query', $meta_query ); 
        }
    }
}
add_action( 'woocommerce_product_query' , 'woo_custom_search' );

But, when I check the query that was run using Query Monitor, I see that Wordpress built the SQL as such:

SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (6) ) )
AND (((wp_posts.post_title LIKE '%83976910%')
OR (wp_posts.post_excerpt LIKE '%83976910%')
OR (wp_posts.post_content LIKE '%83976910%')))
AND ( ( ( wp_postmeta.meta_key = '_sku'
AND wp_postmeta.meta_value LIKE '%83976910%' ) ) )
AND wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'wc-quote'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title LIKE '%83976910%' DESC, wp_posts.post_date DESC
LIMIT 0, 60;

This SQL query doesn't return any products because of the line AND ( ( ( wp_postmeta.meta_key = '_sku'. If I take that SQL and replace AND on the offending line with OR, it works as expected when manually querying in mysql.

So, how do I override the default AND relation here? I want to be able to search the title OR content OR meta_key. The default seems to be searching the (title OR content) AND meta_key.

user_13315636 · Answer

Unfortunately I could not find a proper answer to the question, but I did find a workaround that achieves the desired result: Adding the product's SKU to its short description provides the intended functionality. You could also add it to its name or it's long description and achieve the same result.

To do this, I exported all products to a CSV using the built-in exporter, opened it up in Excel, appended the SKU column to the short description column, and imported the CSV back into the website, updating existing products.

(Sorry this isn't an answer to the question, but it will hopefully assist anyone in a similar boat)

How do I search title, content OR meta in WP_Query?

1 Answers