query : how to get the value of a custom field within a relationship field?

Question

I have a channel 1 and a channel 2. In channel 1 I have a relationship field that relate to channel 2.

I want (by making a query, for reasons I won't explain here because this is not important) to trace the content of a custom field (named "client") of the channel 2.

I know I have to look into exp_relationships table but don't know how to build my query because the columns of exp_relationships are not very relevant to me :D

What I have as material to use at this stage is the ID of the parent entry.

Anyone can help me on this please? Thanks in advance.

Answer 1

The exp_relationships table’s two relevant columns are parent_id and child_id. In your example, the entry_id of the entry in Channel 1 is the parent_id and the entry_id from Channel 2 is the child_id.

So you’ll build your query by selecting fields from the exp_channel_data table, joining to exp_relationships via the child_id, and specifying the entry via the parent_id. I’m using SequelPro, and something like this appears to do the trick…

select field_id_N 
from exp_channel_data 
inner join exp_relationships on exp_channel_data.entry_id = exp_relationships.child_id
where exp_relationships.parent_id = NNN

There are variations. Let’s say you are looking for the title of the child entry, you can skip the exp_channel_data table and go straight for the jugular by linking to the exp_channel_titles table instead, like this…

select title 
from exp_channel_titles 
inner join exp_relationships on exp_channel_titles.entry_id = exp_relationships.child_id
where exp_relationships.parent_id = NNN