Support

Account

Register Login

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)

The topic ‘How to Query Relationship Field’ is closed to new replies.