We use cookies to improve your experience. No personal information is gathered and we don't serve ads. Cookies Policy.

ExpressionEngine Logo ExpressionEngine
Features Pricing Support Find A Developer
Partners Upgrades
Blog Add-Ons Learn
Docs Forums University
Log In or Sign Up
Log In Sign Up
ExpressionEngine Logo
Features Pro new Support Find A Developer
Partners Upgrades
Blog Add-Ons Learn
Docs Forums University Blog
  • Home
  • Forums

[SOLVED] Need to add Event Speaker filter to Event query

Development and Programming

BB Digital's avatar
BB Digital
2 posts
4 years ago
BB Digital's avatar BB Digital

I have a page that displays Events using this query:

{exp:query sql="select a.title, a.url_title, date_format(from_unixtime(b.field_id_82), '%Y') as entry_year, b.field_id_107 as extended_title from exp_channel_titles a left outer join exp_channel_data b on a.entry_id=b.entry_id where a.channel_id=14 and a.status='open' order by b.field_id_82 desc;"}

It simply selects four columns of data, filters by Channel “14” (Events) and Status “Open”, and orders by most recent date first.

When editing the Event Entry in EE Admin, there is a space to choose Event Speakers (none, one or many), and move them from the left to the right side (choosing as many Event Speakers as needed). I need some way to modify the above query so that I can filter it by Event Speaker.

So I might want to say “only show Events where John Smith is a speaker” instead of all Events, as it is now.

I assume this will require joining another table somehow? However, this level of query writing is above my ability.

Hoping for some advice as to how to do this? Thank you.

       
BB Digital's avatar
BB Digital
2 posts
4 years ago
BB Digital's avatar BB Digital

Here is what I came up with, it does seem to work. Took a lot of trial-and-error with the various tables and joins.

select
  a.title,
  a.url_title,
  date_format(from_unixtime(b.field_id_82), '%Y') as entry_year,
  b.field_id_107 as extended_title
 from
  exp_channel_titles a
  left outer join exp_channel_data b on a.entry_id=b.entry_id
  left outer join exp_playa_relationships c on c.parent_entry_id=a.entry_id
  left outer join exp_channel_titles d on c.child_entry_id=d.entry_id
 where
  a.channel_id=14
  and a.status='open'
  and d.url_title='{segment_4}'
 order by
  b.field_id_82 desc;
       
Andy McCormick's avatar
Andy McCormick
183 posts
4 years ago
Andy McCormick's avatar Andy McCormick

Glad you got this to work. You might also look at accessing Events where a speaker is speaking by accessing the parents of the entry. See the docs here https://eeharbor.com/playa/documentation/parents

So you might have something like this:

{exp:channel:entries channel="speakers"}
    <h3>John Smith Featured at the Following Events:</h3>
    <ul>
    {exp:playa:parents channel="events"}
        <li><a href="/events/{url_title}">{title}</a></li>
    {/exp:playa:parents}
    </ul>
{/exp:channel:entries}
       

Reply

Sign In To Reply

ExpressionEngine Home Features Pro Contact Version Support
Learn Docs University Forums
Resources Support Add-Ons Partners Blog
Privacy Terms Trademark Use License

Packet Tide owns and develops ExpressionEngine. © Packet Tide, All Rights Reserved.