How to retrieve specific ACF repeater row with matching sub-field value

Question

I've experimented and researched for hours trying to find a way to get this to work, but I'm just not having any luck.

So I have a couple different custom post types that need to co-mingle a bit. Let me try and explain it as clear and economically as possible.

There are 2 custom post types, Messages & Campuses. (this is a large multi-campus church website). Each Message post will have some repeater rows based on Campuses (through the Taxonomy Field), and each Campus post will be trying to grab the latest Message post, and pull the specific "campus" repeater row, and the same field values from that row.

The ACF repeater section for the Message single post... (we're choosing one of the Campus posts, then manually entering the speaker name and the message URL)

So at the moment, I'm inside of a "Campus" single post, trying to query and get the latest Message post, which is working. But then I'm trying to target and pull in ONLY the values of a specific repeater row INSIDE of the Message post. The repeater fields in the Message are a Campus Select (select box based on the Post Object field (on the "campus" post-type), and 2 different text fields.

To further illustrate... the Message post has the following repeater data: Repeater row 1: Campus Select - Troy (post), Message Speaker - Danny Cox, Message URL - (url A) Repeater row 2: Campus Select - Birmingham (post), Message Speaker - Cliff Johnson, Message URL - (url B) ... and there will be a dynamic number of repeaters for the multiple campuses.

So what I'm trying to do is as follows... Inside of my Campus post - "Troy" for example - I want to grab the latest Message post, go into the repeaters and find the row with the "Campus Select" value. I want to return the Message Speaker (Danny Cox), and Message URL (url A) from ONLY that row. I don't need anything from the other rows.

I have 2 repeater rows in my current "latest message" and here's my current query:

<?php
  $args = array(
    'post_type' => 'messages',
    'posts_per_page' => 1
  );

  $latestMessageQuery = new WP_Query($args);
  if( $latestMessageQuery->have_posts() ) { ?>
    <?php while ($latestMessageQuery->have_posts()) : $latestMessageQuery->the_post(); ?>
      <?php if( have_rows('campus_message') ): ?>
        <?php while( have_rows('campus_message') ): the_row(); ?>
          <?php if( get_sub_field('campus_selector') == 'troy' ): ?>
            <?php the_sub_field('message_speaker'); ?>
          <?php else: ?>
            <?php the_sub_field('message_speaker'); ?>
          <?php endif; ?>
        <?php endwhile; ?>
      <?php endif; ?>
    <?php endwhile; ?>
  <?php }
  wp_reset_query();
?>

For troubleshooting, I'm manually trying to grab the "troy" value "campus selector" instead of getting that value dynamically, based on my current "campus" location...

The query is returning both: "Cliff Johnson Danny Cox" (as I have 2 repeater rows).

How can I return ONLY: " Troy, Danny Cox, url A " in my query?

Answer 1

I am providing more SQL-direct solution using $wpdb global. Normally, I strongly discourage to use SQL "directly", but in this case, structure of WP metas and meta storing by ACF should be stable in future, because ACF flow is to store fields as "normal" meta / custom fields.

I am posting this because it's a pretty simple and redundant-free way on how to get specific ACF repeater row found by a subfield value.
...useful when you have a LOT of ACF repeater rows.

I am going to show you an example on repeater in an options field group, because there I think it is the most relevant.

Let's assume you have option field with key/name your_option_name and subfield with key/name subfield_name - note the \ escaping at some parts, it's because SQL LIKE is using _ as
a special character - meaning "any single character"

  $query = 
 "SELECT option_name, option_value FROM {$GLOBALS['wpdb']->prefix}options"
." WHERE option_name LIKE ("
        ." SELECT"
        ." CONCAT( 'options\_your\_option\_name\_', CAST( REPLACE(option_name, 'options_your_option_name_', '' ) AS INTEGER ), '%' )"
        ." FROM {$GLOBALS['wpdb']->prefix}options"
        ." WHERE"
        ." (option_name LIKE 'options\_your\_option\_name\_%\_subfield\_name')"
        ." AND"
        ." (option_value = 'some subfield value')"
      ." LIMIT 0,1"
." )"
  ;

  $results = $GLOBALS['wpdb']->get_results( $query, ARRAY_A );

  if( !empty($results) ){
    foreach($results as $result){
      //do something with it :)
      echo $result['option_name'] . " => " . $result['option_value'];
      echo "<br>";
    }

  }

Main disadvantage of this solution is that if you have some formatted fields, you have to take care of formatting manually, for wysiwyg field it is quite simple, just use the_content filter like
echo apply_filters('the_content', $wysiwyg_stored_content);.

SQL note: it basically searches for prefix_main_key_NUMBER_subkey then it removes prefix_main_key_ from the key found, so only NUMBER_subkey is left => now if you CAST string to INTEGER in MySQL/MariaDB and it STARTS with an INTEGER, you will get the INTEGER this way... :)

Answer 2

I got some help from ACF Support on this issue, and the following code they sent me got what I was asking for.

<?php
$args = array(
    'post_type' => 'messages',
    'posts_per_page' => 1
);
$current_campus_id = get_the_ID();

$latestMessageQuery = new WP_Query($args);
if( $latestMessageQuery->have_posts() ) {
    while ($latestMessageQuery->have_posts()) : $latestMessageQuery->the_post();
        if( have_rows('campus_message') ):
            while( have_rows('campus_message') ): the_row();
                if( get_sub_field('campus_selector') == $current_campus_id ):
                    the_sub_field('message_speaker');
                    the_sub_field('message_url');
                    break;
                endif;
            endwhile;
        endif;
    endwhile;
}
wp_reset_query();
?>