Support

Account

Home Forums Front-end Issues How to Query Relationship Field

Solved

How to Query Relationship Field

  • I’m trying to figure out how to do a query on a CPT, filtering by a relationship field AND ordering by it. I don’t think something like this can work (and it doesn’t) because that relationship field, “presenter,” is serialized.

    // The Query
    $args = array(
    	'post_type'	=> 'presentations',
    	'posts_per_page'     => -1,					
    	'post_status' => 'publish',
    	'order' => 'ASC',
    	'orderby' => 'presenter',
    	'meta_value' => 'presenter'		
    );														
    Guessing a more custom SQL query is needed, but I'm still not sure how to query on that serialized field.  Ideas would be appreciated!			
    
    $custom_loop = new WP_Query($args);			
    
  • Okay, so I see there is this — http://www.advancedcustomfields.com/resources/filters/acf-fields-relationship-query/

    But I wish there was an example of it in use, not just in the functions.php. I’m still not sure how to actually use it in my case.

  • @relish27

    Change the “orderby” arg to ‘orderby’ => ‘meta_value’ and then specify ‘meta_key’ => ‘presenter’ (rather than ‘meta_value’ => ‘presenter’). This will order the query results by the meta_value of ‘presenter’.

    It sounds like you’re trying to also query posts with a specific ‘presenter’ value. If so, add to the $args array:

    
    'meta_query' => array(
      array(
        'key' => 'presenter',
        'value' => '"' . $presenter_value . '"',
        'compare' => 'LIKE',
      )
    )
    
  • So, later on, I realized that this is not the right way to go about it. Really what I want is a subset of a CPT called “staff.” I want to get the staff who are presenters on my CPT called “presentations.”

    Therefore, it seems like I should do a query on “staff” but just get the ones whose ID is used in the presentations. Right?

    $args = array(
    	'post_type'	=> 'staff-members',
    	'posts_per_page'     => -1,					
    	'post_status' => 'publish',
    	'order' => 'ASC',
    	'orderby' => 'title',
    	'post__in' => $presenter_id_list												
    );

    However, this starts getting way more complicated than it seems like it should… in order to get the $presentation_id_list data, I can get the distinct list of presenters here:

    $presenter_data = $wpdb->get_col( 
    "SELECT DISTINCT wp_postmeta.meta_value FROM wp_posts 
    INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
    WHERE 1=1 AND wp_posts.post_type = 'presentations' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = 'presenters' ) 
    GROUP BY wp_posts.ID ");

    But that gives me an array of the serialized “presenter” relationship field. How do I get at the ID in that relationship field??? This seems like it is getting way more complicated than it needs to be. Am I missing something?

    Thanks!

  • Also, to clarify, the value I’m getting back for the presenter in that $presenter_data query is a serialized item like this:

    a:1{i:0;s:2:"71";}

    71 is the ID I’m trying to get. I realize I could use some PHP to manipulate this, just seems like there must be a better way.

  • Okay, this works. But, holy schnikes, that’s a lot of work for one little list!

    <label>Presenter: </label>
    <?php
    #----------------- GET THE DISTINCT LIST OF SERIALIZED PRESENTERS FROM PRESENTATIONS																		
    $presenter_id_array = array();
    $presenter_data = $wpdb->get_col( 
    "SELECT DISTINCT wp_postmeta.meta_value FROM wp_posts 
    INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
    WHERE 1=1 AND wp_posts.post_type = 'presentations' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = 'presenters' ) 
    GROUP BY wp_posts.ID ");		
        
    #----------------- LOOP THROUGH THAT LIST										
    foreach ($presenter_data as $presenter_data_item)
    {
        #----------------- DO A QUERY TO GET A SPECIFIC PRESENTATION POST
        $args = array(
            'post_type'	=> 'presentations',
            'posts_per_page'     => -1,					
            'post_status' => 'publish',											
            'meta_query' => array(
              array(
                'key' => 'presenter',
                'value' => $presenter_data_item,
                'compare' => '=',
              )
            )																							
        );																	                                       
        
        $custom_loop = new WP_Query($args);	
        #echo "<br /><br >". $custom_loop->request; 	
        
        #----------------- GET THAT PRESENTATION'S DATA
        if( $custom_loop->have_posts() ):						
        
            while( $custom_loop->have_posts() ): $custom_loop->the_post(); 
            
                #----------------- GET THE PRESENTER RELATIONSHIP FIELD DATA
                $posts = get_field('presenter');
                 
                if( $posts ): 
                    foreach( $posts as $post_object): 
                        
                        #----------------- FINALLY, GET THAT STAFF ID OUT OF THE RELATIONSHIP FIELD!!!!
                        #----------------- ADD IT TO MY LIST OF STAFF IDS
                        array_push($presenter_id_array,$post_object->ID);
                    endforeach; 
                endif;																																																								
            endwhile;        
        endif;																										
    }									
                                        
    #----------------- NOW, DO A QUERY FOR A SUBSET OF STAFF WHO ARE PRESENTERS
    $args = array(
        'post_type'	=> 'staff-members',
        'posts_per_page'     => -1,					
        'post_status' => 'publish',
        'order' => 'ASC',
        'orderby' => 'title',
        'post__in' => $presenter_id_array												
    );																	                                       
    
    $custom_loop = new WP_Query($args);	
    #echo "<br /><br >". $custom_loop->request; 	                                       												
    
    ?><select name="search_presenter">
        <option value="">-</option>
            <?php if( $custom_loop->have_posts() ):						
            
                while( $custom_loop->have_posts() ): $custom_loop->the_post(); 
                    
                    ?><option value="<?php get_the_id(); ?>"><?php the_title(); ?></option><?php
                endwhile;        
            endif;
            
            // Reset Query
            wp_reset_postdata(); ?>
    </select>                                    
    
  • I got stuck on this just now, I was able to solve the issue using the last example code in this tutorial:

    http://www.advancedcustomfields.com/resources/tutorials/querying-relationship-fields/

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

You must be logged in to reply to this topic.

We use cookies to offer you a better browsing experience, analyze site traffic and personalize content. Read about how we use cookies and how you can control them in our Cookie Policy. If you continue to use this site, you consent to our use of cookies.