Query relationship field in repeater field

• joostvanhoof

• August 21, 2013 at 2:18 am

I have the following setup:

• Custom post type ‘events’
• Custom post type ‘artists’

For each event, there’s a repeater field which holds

• A day (select)
• The relationship field to select posts ‘artists’.

Now if I have an artist ID, how can I run a query to find all events with that artist?

post is about querying fields which are inside a repeater field, but it doesn’t go into querying relationship fields which are inside repeater fields.

I think the solution is in altering the query described in the post:

$rows = $wpdb->get_results($wpdb->prepare( 
            "
            SELECT * 
            FROM wp_postmeta
            WHERE meta_key LIKE %s
                AND meta_value = %s
            ",
            'images_%_type', // meta_name: $ParentName_$RowNumber_$ChildName
            'type_3' // meta_value: 'type_3' for example
));

Then the question is: what do I put for ‘type_3’? I have the post ID of the artist I want to query, but putting the post ID instead of ‘type_3’ doesn’t work. That’s because all the artists are in a relationship field which is an array right? What do I put instead of ‘type_3’ to query on the artist post ID?

After I have the answer to that, I can alter the query (or put this query in another query) to get all events with this artist ID (now it’s only querying wp_postmeta for the current event).

Thanks!

• Elliot

• August 21, 2013 at 2:31 pm

Hi @joostvanhoof

I do not believe it is possible to query WP in this way.

If you chanced from the relationship field to the post_object field, your issue would be solved. Is this possible?

Also, with this kind of advanced querying, your life will be a lot easier if you use a plugin made for that exact purpose – posts 2 posts

• joostvanhoof

• August 21, 2013 at 11:37 pm

Hi Elliot,

Thanks for your reply. I had another question on this forum where you also suggested to use posts2posts. It looks like it’s the solution, but I don’t want to rely on another plugin (which has cancelled support). So I’m trying to find a solution with only using ACF.

I have about 700 events and 8000 artists, so changing the relationship field to a post_object field isn’t an option. Then I’d have to select an artist out of a drop-down with 8000 artists…

Every event can have more than 100 artist, so I hope my whole concept won’t be limited by performance issues.

I did notice that I can put the Return Format from Post Objects to Post IDs. So on an event page, for each artist I can do:

$artist = get_field('artist');
foreach ($artist as $artist_id) {
	echo get_permalink($artist_id);
        echo get_the_title($artist_id);
	etc...
}

If I look at the way all the artists are stored in the DB for each event, it is:

Array ( [0] => 10001 [1] => 10002 [2] => 10003 [3] => 10004 [4] => 10007 [5] => 10008 [6] => 10009 [7] => 17497 )

Which looks much leaner than when I put the relationship setting to the return format Post Objects (because than it saves a lot more artist postdata in the array, which I don’t really need since I can get all postdata with just the ID).

If I make a repeater field, in which I put a relationship field which has Return Format set to Post IDs, would that simplify my query? Is it then possible to find all events which have one particular artist post ID?

Thanks in advance!

• joostvanhoof

• August 22, 2013 at 12:56 am

I just gave p2p a shot and it indeed does exactly what I want.

Going through the documentation, I’ve found this (see here):

// Create connection
p2p_type( 'YOUR_CONNECTION_TYPE' )->connect( $from_id, $to_id, array(
	'date' => current_time('mysql')
) );

// Delete connection
p2p_type( 'YOUR_CONNECTION_TYPE' )->disconnect( $from_id, $to_id );

It is possible to use ACF with my setup as described above (artists custom post types as relationship fields in repeater fields) and then add the above p2p code when I’m adding and deleting artists?

Because then I can:

• Use my preferred ACF setup (relationship fields in repeater fields)
• Which results in that I don’t have to use the p2p admin box for adding artists
• I can use the p2p queries which solves my querying problem!

The only question that remains is: what happens when I add an artist in one repeater block, and then add the same artist in another repeater block? The documentation of p2p says about the code above: ‘The advantage of using this API is that it enforces all the rules you set when defining the connection type, such as ‘prevent_duplicates’ and ‘cardinality’.’ So I guess it will check for duplicates? The link to this p2p documentation here.

I think we’re almost there :-).

• Elliot

• August 22, 2013 at 2:25 pm

Hi @joostvanhoof

Yes, it would be possible to hook into the acf/update_value filter and update or remove connections based on the $value.

This would be a very good solution, however, will require some complex coding which will be up to you.

As to your previous statement regarding post ID / object:
The relationship field will always save the ID’s, never any information about the actual $post. No change will be made to the DB by this option, only the returned value via get_field.

Basically, having the relationship field within the repeater prevents querying from being possible. If the relationship field is on the root level, then yes, you can easily query backwards and forwards from artists to events (there is a tutorial for this on the docs page).

I use p2p in my advanced websites and also, so does bbPress – the wordpress forum plugin!

Good luck

Cheers
E

• Elliot

• August 22, 2013 at 2:26 pm

Your other solution is to create a totaly custom field type for this day => artist data.

There is a tutorial for this too!

• joostvanhoof

• August 22, 2013 at 6:08 pm

Hi Elliot,

Everything is clear now, thanks a lot for your help!

Would be awesome if I could to the complex coding, unfortunately I can’t :-).

It seems that I should go with the relationship field on the root level. Because when I’d use p2p, I’d have to do the same (since p2p can’t be in a repeater field). On the other hand I think the p2p query will be faster due to the architecture of having a separate DB for connections, do you I agree?

I’ll mark this question as solved.

Cheers,
Joost

• Nathan

• September 11, 2013 at 12:10 pm

Hi Elliot,

In you original reply in this thread, you allude to this being possible using the post_object field rather than the relationship field, is that correct?

I’ve got a repeater field on a page for listing awards, which contains sub fields for ‘award-name’, ‘award-year’ and ‘award-project’ (the last of which is a post_object field, limited to the ‘projects’ custom post type.

I’m looking to query that on the single projects template, to display the name and year of any awards that have that project set for award-project.

(I know it would be much easier to do it the other way around, having the award set on the project itself and then querying them on the awards page, but I need to allow for some awards not having a project associated with it.)

Cheers

• Elliot

• September 11, 2013 at 2:03 pm

Hi @Nathan

Yes, if the post_object field was used and only a single value was saved per repeater row, you could use this guide to query the posts that contiain a sub field value!

http://www.advancedcustomfields.com/resources/tutorials/querying-the-database-for-repeater-sub-field-values/

Good luck

Cheers
E