Need to search a custom field (ingredients, one long string per post), but want it to allow phrases/non-exact matches

Question

So I have a complex list of ACF repeaters for ingredients, but have a save hook to store them all as one comma delimited text string in a custom field to help. My current "advanced" search passes ingredient searches as a meta query and works, but non-exact matches return nothing. Like if you search "pepper and paprika" it fails, but if you search "pepper" or "paprika" there are common posts (recipes).

Is there a way to make a meta query work like a normal query a little bit more?

My meta_query:

    $args['meta_query'] = array(
        array(
            'key' => 'ingredient_list',
            'value' => $search_query,
            'compare' => 'LIKE',
        )
    );

Thank you for any help!

Answer 1

You could break apart the search-string into an array and add each word to it's own meta-query

$search_query = 'strawberries and cream';
$search_query_array = explode(' ', $search_query); // turn search query string into array of words
$stop_words = ['and','or','that','have','with','this'];

if ( ! empty( $search_query_array ) ) {
    foreach( $search_query_array as $index ) { // remove any stop-words from the search query array
        if ( in_array($search_query_array[$index], $excluded_words) ) {
            unset( $search_query_array[$index] );
        }
    }
}

$args['meta_query'] = [
    'relation' => 'OR', // empty meta_query with or condition
];
if ( ! empty( $search_query_array ) ) {
    foreach( $search_query_array as $index ) { // remove any stop-words from the search query array
        $word = sanitize_key( $search_query_array[ $index ] );
        $args['meta_query'][ 'search_query__' . sanitize_key( $word ) ] = [ // give the meta query a handle for easy access & debugging
            'key' => 'ingredient_list',
            'value' => sanitize_key( $word ), // or '"' . sanitize_key( $word ) . '"',  or '%' . sanitize_key( $word ) . '%'
            'compare' => 'LIKE', 
        ]
    }
}

var_dump( $args ); // the final meta_query, just needs to be added to wp_query

Answer 2

I ended up having to build a custom query on the indexed custom field of ingredients for performance. Under load the meta_queries slowed way down. Not quite the answer I wanted, but it works.