Using pre_get_posts for meta value of LIKE comparison on ACF repeater sub field

Question

I'm trying to organize my search results page based on an ACF subfield value of a repeater field. I would like a LIKE result rather than =.

So far, I got the following (modified with help below, but still not working):

// Modify meta key to allow wildcard
function add_wildcard_to_meta_key_filter( $where ) {

  $where = str_replace("meta_key = 'test_repeater_%", "meta_key LIKE 'test_repeater_%", $where);

  return $where;
}
add_filter('posts_where', 'add_wildcard_to_meta_key_filter');

//Modify search query
function alter_search_query($query) {
  if ( !$query->is_search )
    return $query;

  $search = $query->query;

  $query->set('post_type' ,'page');
  $query->set('meta_query', array(
    array(
      'meta_key' => 'test_repeater_%_test_sub_field',
      'meta_value' => '%'.$search.'%',
      'compare' => 'LIKE',
    ),
  ));

  $query->set('s', '');

}
add_action('pre_get_posts','alter_search_query');

It works fine if I add the exact value, i.e. "beef tenderloin", but if I enter "beef" it fails. How do I get the search to be more generic?

I installed the Query Monitor plugin and noticed I got the following SQL

SELECT SQL_CALC_FOUND_ROWS wpfb_posts.ID
FROM wpfb_posts 
INNER JOIN wpfb_postmeta
ON ( wpfb_posts.ID = wpfb_postmeta.post_id )
WHERE 1=1 
AND ( ( wpfb_postmeta.meta_key LIKE 'test_repeater_%_test_sub_field'
AND wpfb_postmeta.meta_value IN ('beef') ) )
AND wpfb_posts.post_type = 'page'
AND (wpfb_posts.post_status = 'publish'
OR wpfb_posts.post_status = 'acf-disabled'
OR wpfb_posts.post_status = 'private')
GROUP BY wpfb_posts.ID
ORDER BY wpfb_posts.post_date DESC
LIMIT 0, 10

If I change this:

AND ( ( wpfb_postmeta.meta_key LIKE 'test_repeater_%_test_sub_field'
AND wpfb_postmeta.meta_value IN ('beef') ) )

To this:

AND ( ( wpfb_postmeta.meta_key LIKE 'test_repeater_%_test_sub_field'
AND wpfb_postmeta.meta_value LIKE '%beef%' ) )

And run that in phpMyAdmin's SQL, I get the desired row. The question is, how can I change the query to look like that using WordPress functions?

Answer 1

You need to add Wildcards to your meta_value.

Change

$query->set('meta_query', array(
    array(
      'meta_key' => 'test_repeater_%_test_sub_field',
      'meta_value' => $search,
      'compare' => 'LIKE',
    ),
  ));

To

$query->set('meta_query', array(
    array(
      'meta_key' => 'test_repeater_%_test_sub_field',
      'meta_value' => '%'.$search.'%',
      'compare' => 'LIKE',
    ),
  ));

Answer 2

first you need to loop how many times repeater has values example

anada_number_0_anada_number
anada_number_1_anada_number
anada_number_2_anada_number

In my case i have five times repeater field

for( $i=0; $i<5; $i++ ){ 
                    $meta_query['anada_number'][] = array(
                        'key' => 'anada_number_'.$i.'_anada_number',
                        'value' => $anada_number,
                    );
                }

then use it in query arg

$arg = array(
$meta_query['anada_number']
);

$wp_query = new WP_Query($arg);