Repeater subfields not working (with array)

• fredjs

• June 21, 2017 at 3:07 am

Hey, everyone,

Having a problem with my code and am hoping someone can cast their peepers on it for me. I’m using an array to only display posts from a CPT when all three values match, but the problem is that while the year value is set to 2017, it’s also displaying content for 2016 if the other two subfield values are right.

Here’s the code from the template:

<?php
			// args
			$args = array(
				'posts_per_page'	=> -1,
				'post_type'		=> 'profiles',
				'meta_query'	=> array(
					'relation'		=> 'AND',
					array(
						'key'		=> 'awards_given_%_year',
						'compare'	=> '=',
						'value'		=> '2017',
					),
					array(
						'key'		=> 'awards_given_%_program',
						'compare'	=> '=',
						'value'		=> 'Media Relations',
					),
					array(
						'key'		=> 'awards_given_%_level',
						'compare'	=> '=',
						'value'		=> 'GRAND PRIZE',
					)
				)
			);

			// query
			$the_query = new WP_Query( $args );
			?>
			
			<?php if( $the_query->have_posts() ): ?>
			<?php while ( $the_query->have_posts() ) : $the_query->the_post(); ?>
			
				<?php if(get_field('awards_given')): ?>
				<?php while(has_sub_field('awards_given')): ?>
	
				<?php if( get_sub_field('level') == ('GRAND PRIZE') ): ?>
			
				<!-- begin grand prize winner -->
				<article class="snippet">
					
					<a href="<?php the_permalink(); ?>">
						<?php the_post_thumbnail( 'post-thumbnail', array(
						'class' => 'attachment-post-thumbnail'
						) ); ?>
					</a>
					
					<h3><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></h3>
						
					<?php if( get_sub_field('year') ): ?>	
						
					<p class="award-category"><strong><?php the_sub_field('program_category'); ?></strong></p>
					<p>"<?php the_sub_field('campaign_title'); ?>"</p>
					<span class="prize-level circle-prize grand-prize"><i class="icon-star"></i><?php the_sub_field('level'); ?></span><br><br>
					
					<?php endif; ?>
				
				</article>
				
				<?php endif; ?>

				<?php endwhile; ?>
				<?php endif; ?>

			<?php endwhile; ?>
			
			<hr>

			<?php endif; ?>
			
			<?php wp_reset_query();	?>

And here’s what I’m using in the functions.php file:

function my_posts_where( $where ) {
    
    $where = str_replace("meta_key = 'awards_given_%", "meta_key LIKE 'awards_given_%", $where);

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

Any insight would be really appreciated!

Cheers!

• John Huebner

• June 21, 2017 at 9:23 am

The main problem here is that there is no way at all, using WP_Query to match up the values represented by the % pattern in your field names. So for example, it will match if the combination of awards_given_1_year, awards_given_2_program and awards_given_3_level meet the requirements.

The number of queries you’d need to do with WP_Query would be uncountable.

It might be possible to remove the % and add enough meta_query conditions to do the work, but this would in all likelihood, time out the page.

This cannot even be done with a single query using $wpdb, because there is no single MySQL query that can be constructed to do the work. I have looked into this, and have asked the question in various places, including here https://dba.stackexchange.com/questions/118271/matching-same-unknown-characters-in-two-columns, knowing that I would probably never get an answer to the question, but not wanting to give up on the idea.

Anyway, I just wanted to give you a little background on the difficulty of doing this type of querying.

This is how I solve the question. 1st thing is that I never use repeaters for anything that I may want to search on if I can help it at all. I will spend days looking at alternate solutions. I’m pretty sure that this is too late for your current project because I’m pretty sure that this question reminds me of previous questions here related to the same type of setup of fields.

2nd, if I must use a repeater and cannot find another solution, or for some reason it’s too late to change it, then what I do is I create an acf/save_post filter that gets the values from the repeater and puts them into a form that is searchable by much simpler methods.

// priority 20 is after ACF is done
add_action('acf/save_post', 'convert_repeater_to_wp', 20);

function convert_repeater_to_wp($post_id) {
  if (get_post_type($post_id) != 'profile') {
    // not the right post type
    return;
  }
  // create a meta key to store the new values under
  $meta_key = 'combined_awards_given_repeater'
  // clear all of the existing values for the field before adding new values
  // to make sure it's update correctly
  delete_post_meta($post_id, $meta_key);
  // now we add the values of the repeater to this field
  if (have_rows('awards_given')) {
    while(have_rows('awards_given')) {
      $value = get_sub_field('year').' '.
                 get_sub_field('program').' '.
                 get_sub_field('level');
      add_post_meta($post_id, $meta_key, $value, false);
      // the last value "false" tells WP that this meta key can have multiple values
    } // end while have rows
  } // end if have rows
} // end function

with the above function in place you can now alter your query to

$args = array(
  'posts_per_page'  => -1,
  'post_type'    => 'profiles',
  'meta_query'  => array(
    array(
      'key'    => 'combined_awards_given_repeater',
      'compare'  => '=',
      'value'    => '2017 Media Relations GRAND PRIZE',
    )
  )
);

the only hurdle you have is getting the existing values into this field. You can either manually open and update every post if the number is limited… or you can build a cron to do the work by getting every post of the post type, looping through them all and doing the same thing that the function does for every post.

• fredjs

• June 22, 2017 at 5:56 am

Jeez, that was far more help than I expected! Thank you! I’ll give this a try and post my results. Cheers!