Support

Account

Home Forums Add-ons Repeater Field Query repeater field with two values

Solving

Query repeater field with two values

  • Hi,

    I have a custom post type Films with a repeater field Schedule. In this repeater field, i have two sub-fields : Dates (select) and Locations (select). I want to display only films which have both sub-fields selected and group by locations, like this :

    DATE 1

    Location 1
    Film A
    Film B

    Location 2
    Film B
    Film C

    The meta_query display all posts which have one date or one location selected not both. The “And” parameter don’t work or not by row.

    // filter
    function my_posts_where( $where ) {
        
        $where = str_replace("meta_key = 'seances_%", "meta_key LIKE 'seances_%", $where);
    
        return $where;
    }
    
    add_filter('posts_where', 'my_posts_where'); ?>
    
    <?php // CHAMPS LIEUX
    $lieux_key = "field_5880dc1d1f784";
    $lieux = get_field_object($lieux_key);
    
     ?>
    
        <?php // args
        $args = array(
            'post_type'     => 'film',
            'suppress_filters' => false,
            'meta_query'    => array(
                'relation'      => 'AND',
                array(
                    'key'       => 'seances_%_lieu',
                    'compare'   => 'LIKE',
                    'value'     => 'cineville',
                ),
                array(
                    'key'       => 'seances_%_jour2',
                    'compare'   => 'LIKE',
                    'value'     => 'vendredi10mars',
                )
            )
        );
        $the_query = new WP_Query( $args ); ?>
    
        <h3>Vendredi 10 mars</h3>
        <?php if($the_query->have_posts()) : while ( $the_query->have_posts() ) : $the_query->the_post(); ?>
            <p><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></p>
        <?php endwhile; endif;  wp_reset_query(); ?>
    

    I try this code too but it display each location for each film like this :

    DATE 1

    Location 1
    Film A

    Location 1
    Film B

    <?php
    
            $rows = $wpdb->get_results($wpdb->prepare( 
                "
                SELECT * 
                FROM {$wpdb->prefix}postmeta
                WHERE meta_key LIKE %s
                    AND meta_value = %s
                ",
                'seances_%_jour2',
                'vendredi10mars' 
            ));
    
        // loop through the results
        if( $rows ) :
            foreach( $rows as $row ) : 
    
                preg_match('_([0-9]+)_', $row->meta_key, $matches);
                $meta_key = 'seances_' . $matches[0] . '_lieu'; // $matches[0] contains the row number
                $lieux = get_post_meta( $row->post_id, $meta_key, true );
            ?>
    
                    <h3><?php echo $lieux;?></h3>
                    <p> 
                        <a href="<?php the_permalink( $row->post_id ); ?>">
                            <?php echo get_the_title( $row->post_id ); ?>
                        </a>
                    </p>
    
            <?php endforeach; ?>
        <?php endif; ?>

    Can you help me ? Is there a solution to query two sub-fields in same row for repeater field ?

    Thanks.
    A.

  • It is impossible to do a query based on two sub fields of a repeater where you want to get posts where sub field A == X AND sub field B == Y, at least not in a single query. It would take several queries and a lot of php work. It’s the nature of how the sub fields are stored in the DB.

    I have replied to several topics with an alternate means which makes repeater values easier to query. In this case I would save both values of each sub field into a single alternate meta field so that I could use that to query by. You can see examples of the basic idea here https://support.advancedcustomfields.com/forums/topic/query-last-row-of-a-repeater-field/ and here https://support.advancedcustomfields.com/forums/topic/sorting-admin-column-with-repeater/ and I’m sure there are others.

  • To do this with queries on the existing fields you would need to

    1) use $wpdb to query the post meta table to get all of the rows where
    {$repeater_name}\_%\_{$sub_field_name_1} == "X"

    2) Then you’d need to loop though the results of this query, get the post value and the meta key and then you’d need to do another query where
    {$repeater_name}\_{$index_from_first_query}\_{$sub_field_name_2} == "Y"

    3) From this you can construct a list of post ID and use that list in for the post__in argument of a WP_Query.

    It really is much easier to use an acf/save_post filter to convert the values into something that can be more easily searched.

  • Hi John,

    Thank you for your replies. Despite several tests, i can’t do it. Have you a concrete example of the second method ?

    Also, I have tweaked something to arrive where i want but the problem is that if the choice is not checked its value appears anyway even if there are no posts related. The result is :

    DATE 1 (taxonomy)

    Location 1 (taxonomy)
    Film A
    Film B

    Location 2
    Film B
    Film C

    Location 3

    If Location 3 is empty, i don’t want to display it…

    I think your method is best than mine 😉

        <?php // CHAMPS LIEUX
    
        $jours_key = "field_5886014c7f9f1";
        $jours = get_field_object($jours_key);
    
        foreach($jours['choices'] as $k => $v) : 
    
        $lieux_key = "field_5880dc1d1f784";
        $lieux = get_field_object($lieux_key); 
    
        ?>
    
        <h3><?php echo $v; ?></h3>
    
        <?php foreach($lieux['choices'] as $cle => $valeur) :
    
        $args = array(
            'post_type'     => 'film',
            'meta_query'    => array(
                'relation'      => 'AND',
                array(
                    'key'       => 'seances_%_jour2',
                    'compare'   => 'LIKE',
                    'value'     => $k,
                )
            )
        );
        $the_query = new WP_Query( $args ); ?> 
    
        <h3 style="color:#ccc"><?php echo $valeur; ?></h3>
      
        <?php if($the_query->have_posts()) : while ( $the_query->have_posts() ) : $the_query->the_post(); ?>
    
                <?php if( have_rows('seances') ): while( have_rows('seances') ): the_row(); 
                    $date = get_sub_field('jour2');
                    $lieux = get_sub_field('lieu');
                    $horaire = get_sub_field('horaire'); ?>
    
                    <?php if ($date['label'] == $v && $lieux['label'] == $valeur) : ?>
                        <p><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></p>
                        <p><?php echo $horaire; ?></p>
                    <?php endif; ?>
    
                <?php endwhile; endif; ?>
    
        <?php endwhile; endif;  wp_reset_postdata(); ?>
    
        <?php endforeach; ?>
        <?php endforeach; ?>
  • I don’t have a concrete example of the second method, basically because I would never to it that way. It far too complicated and too much work to achieve.

    I would use the first method and create an acf/save_post action. In this action I would loop through the repeater and create another custom post meta field that is a concatenation of the two fields I want to search. I would then do my WP_Query based on this new field that is stored in standard WP fashion. I know that this seems like it’s coming at the problem backwards, but it’s really the easiest way.

  • Thanks, i think i’m limited in PHP to do that… I never work with save_post action and concatenation of fields. I continue to try.

  • The acf/save_post action is detailed here https://www.advancedcustomfields.com/resources/acfsave_post/. You want it to run after ACF saves the data.

    In the filter you would loop through the repeater and get the values from the sub fields that you want to search. You would put these values into additional custom fields, not ACF custom fields, just WP custom meta fields, in some type of format that makes them easier to search for your specific needs.

    You’re needs are more complicated that I’ve dealt with before and I can’t really say what the best format for the values would be.

  • Hi John,

    Thanks for your time !
    I changed my way for this, I think it was too complicated.

    Now, I have two custom post types : FILM and SCHEDULE
    I create my FILM with basic custom fields. And for SCHEDULE, I have two Taxonomies (Date and Location), a custom field for Time and a custom relation field for the films. So, now it’s more easy !

    I have tweak an auto create title for the schedule with the custom fields. And my page display well :

    DATE 1

    Location 1
    Film A
    Film B

    Location 2
    Film B
    Film C

    DATE 2

    Location 1
    Film A
    Film C

    Everything seems to be ok for the moment with that.

  • Please could you help me figure out how to run a WP_Query with meta_query such that posts-types are filtered by a subfield within a group field.

    I have a custom post type of image and an acf group field of authors with a subfield of people. People is a multi-select post_object field. What would I need to do to get images only where people contains a certain id?

    I’m trying something like this based on John Huebner’s comment above.

    $images_query = new WP_Query([
        'posts_per_page' => -1,
        'post_type' => 'images',
        'meta_query' => [
            [
                'key' => 'authors_%_people',
                'value' => ['85'],
                'compare' => 'IN'
            ],
        ],
    ]);

    I’ve clearly got something wrong, Nothing is working. Please help.

  • Have you added this code to functions.php ?

    function my_posts_where( $where ) {
        global $wpdb;
        $where = str_replace(
                  "meta_key = 'authors_", 
                  "meta_key LIKE 'authors_",
                  $wpdb->remove_placeholder_escape($where)
        );
        return $where;
    }
     
    add_filter('posts_where', 'my_posts_where');
  • No, I didn’t realise that was needed. I have tried this now but still no luck. Is ‘compare’ => ‘IN’ correct? And is it correct to put the id for people in an array?

  • I think compare would be LIKE and the value not in the array but ‘”85″‘ or 'value' => '"' . get_the_ID() . '"'

  • I have this code example for a project if it helps (it’s not WP_Query but get_posts, seance is my custom post type, seances a repeater field and film is a relationship field) :

    <?php 
                            $today = date("Ymd");
                            $date = strtotime($today);
                            $seances = get_posts(array(
                                'suppress_filters' => FALSE,
                                'post_type' => 'seance',
                                'meta_key'  => 'date',
                                'orderby'   => 'meta_value_num',
                                'order'=>'ASC',
                                'meta_query' => array(
                                    'relation'      => 'AND',
                                    array(
                                        'key' => 'seances_%_film', // name of custom field
                                        'value' => '"' . get_the_ID() . '"', // matches exaclty "123", not just 123. This prevents a match for "1234"
                                        'compare' => 'LIKE'
                                        ),
                                     array(
                                        'key'       => 'date',
                                        'compare'   => '>=',
                                        'value'     => $today,
                                    )
                                    )
                                ));
                        ?>

    And in functions.php

    function my_posts_where( $where ) {
        global $wpdb;
        $where = str_replace(
                  "meta_key = 'seances_", 
                  "meta_key LIKE 'seances_",
                  $wpdb->remove_placeholder_escape($where)
        );
        return $where;
    }
     
    add_filter('posts_where', 'my_posts_where');
  • Thanks. This is pretty much the same as I have. Although I did not have suppress_filters = FALSE in there, so I tried adding that. Still nothing. Do you think it could be something to do with the group field being different to the repeater?

  • Maybe the problem is the multi-select field … Can you paste all your query ?

  • I am leaving a lot of stuff out because there’s way too much to paste here, but you get the idea.

    <?php
    $author = 85; //get_query_var('author');
    $images_query = new WP_Query([
        'suppress_filters' => FALSE,
        'posts_per_page' => -1,
        'post_type' => 'images',
        'meta_query' => [
            //'relation' => 'OR',
            //'relation' => 'AND',
            [
                'key' => 'authors_%_people',
                'compare' => 'LIKE',
                'value' => '"'.$author.'"',
            ],
        ],
    ]);
    ?>
    <div class="row grid">
    <?php
    if( $images_query->have_posts() ) :
    while( $images_query->have_posts() ) :
    $images_query->the_post();
    ?>
    <div id="<? the_ID() ?>" class="grid-item">
    <?= wp_get_attachment_image( get_field('image')['ID'] ?>
    </div>
    <?php
    endwhile;
    endif;
    wp_reset_query()
    ?>
    </div>
  • Let me know if you have any ideas, I’m going mad.

  • Ok, so I’ve narrowed it down to being a problem with my querying subfields inside a group field because I’ve tried the following:

    • created a test_people post_object field outside of the authors group
    • Keeping the return format as post object
    • Wrap the value in an array

    I’m able to get results with this code:

    $author = 85;
    $images_query = new WP_Query( array(
        'post_type' => 'images',
        'posts_per_page' => -1,
        'meta_query' => array(
            array(
                'key' => 'test_people',
                'value' => array( '"'.$author.'"' ),
                'compare' => 'LIKE',
            ),
        ),
    ));

    *wrapping the value in an array causes another problem but I want to solve the first:
    ( Warning: trim() expects parameter 1 to be string )

    The point it is, I’m not finding a way to query subfields with the function and this syntax:
    'key' => 'authors_%_people'

    Am I wrong in thinking groups can be treated like repeaters?

  • @laconfiserie, thanks for your help and suggestions yesterday, I still not cracked it. I’ve moved my issue to a new forum thread because it’s really a separate issue to this one, please come over there to chip in if you have any thoughts and to continue this. Thanks again.
    https://support.advancedcustomfields.com/forums/topic/wp_query-with-meta_query-on-a-subfield-within-a-group-field/

  • Sorry for the absence, I hope you have solved the problem !

Viewing 20 posts - 1 through 20 (of 20 total)

The topic ‘Query repeater field with two values’ is closed to new replies.