[SOLVED] Need to add Event Speaker filter to Event query
Development and Programming
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.
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;
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}