Thread

I need a bit of help with a query to pull entries where either of two relationship custom fields can equal a certain value, specifically I’ve got a weblog of events involving a home and away team selected via relationship dropdowns, and I want to be able to display the entries where either the home or away team can equal a certain club. I thought I might be able to use the weblog entries search parameter but it seems it only does search:custom_field1=”” AND search:custom_field2=”“, not OR.

Looks like you’re right about the search: parameter… it doesn’t seem like you can do OR relationships between multiple fields (someone correct me if I’m wrong).

This may be a roundabout way of going about it, but you can use the query module to handle your more complicated search logic. It does require some inner knowledge of how the EE database is set up, but it’s not too hard to get a handle on (check out exp_channel_fields and exp_channel_data with phpMyAdmin for those who are curious). I tested and confirmed that the following works:

{exp:query sql="SELECT field_id AS custom1_id FROM exp_channel_fields WHERE field_name='custom_field1'" }
{exp:query sql="SELECT field_id AS custom2_id FROM exp_channel_fields WHERE field_name='custom_field2'" }
{exp:query sql="SELECT entry_id AS queried_entry_id FROM exp_channel_data WHERE field_id_{custom1_id}='XXXXXXX' OR field_id_{custom2_id}='YYYYYYYY'" }


{exp:channel:entries entry_id="{queried_entry_id}" dynamic="off"}

{title}


{/exp:channel:entries}


{/exp:query}
{/exp:query}
{/exp:query}

You’ll need to replace:
custom_field1 : replace with whatever your first field is called
custom_field2 : replace with whatever your second field is called
‘XXXXXXX’ : replace with whatever you want custom_field1 to equal
‘YYYYYYY’ : replace with whatever you want custom_field2 to equal

I’ll gladly explain if you or anyone else is curious. I’d also love to hear if there’s a simpler way to go about this.

Thanks 4-ee, I don’t actually need the first two queries as I already know the IDs for the fields I’m targetting and I’m afraid it’s a bit more complex because the custom fields are relationship fields which means I need to bring exp_relationships into my query which is why I’m having trouble - not too good when it comes to joining tables etc.

Ah yes, that makes things a lot more difficult.

Let’s see here… this should do the trick. I read your post a few times to make sure I had the right idea, and I believe I’ve got something that fits your problem. Also, you seem to understand the custom fields and field ids, so I’ll get right to the meat of the query:

{exp:query sql="
    SELECT cd.entry_id AS queried_entry_id
    FROM exp_channel_data AS cd 
        INNER JOIN exp_relationships AS r1 ON cd.field_id_[HOME_REL_FIELD_ID]=r1.rel_id
        INNER JOIN exp_relationships AS r2 ON cd.field_id_[AWAY_REL_FIELD_ID]=r2.rel_id
    WHERE r1.rel_child_id IN 
            (SELECT entry_id 
            FROM exp_channel_data
            WHERE field_id_[TEAM_NAME_FIELD_ID]='[TEAM NAME]')
        OR r2.rel_child_id IN 
            (SELECT entry_id 
            FROM exp_channel_data
            WHERE field_id_[TEAM_NAME_FIELD_ID]='[TEAM NAME]')
    "}

{exp:channel:entries entry_id="{queried_entry_id}" dynamic="off"}
    <h1>{title}</h1>
{/exp:channel:entries}

{/exp:query}

Here it is in english:

give me the entries where the home_team is in (
      give me all entries where the team name == “xxxx”
  ) OR the away_team is in (
      give me all entries where the team name == “xxxx”
  )

Also, you’ll notice that inner query occurs twice… I’m sure there’s a way to run it only once, but I can’t think of it at the moment.

Excellent, that did it! Thanks very much for your help. 😊