Query to select from categories from different groups (with OR logic)
Development and Programming
I’m trying to pull in a list of events from a channel that spans two category groups. One is an industry sector, whilst the other is event type.
I need to write a query that will pull in all entries from a specific sector category (in this instance category id 6) AND at least one event category (175 or 177).
So far I have this:
<ul>
{exp:query sql="SELECT
type.cat_id,
type.cat_name,
type.cat_url_title,
t.title as title,
t.status,
d.channel_id,
d.site_id,
d.field_id_32 as date
FROM
exp_categories type JOIN
exp_category_posts p ON type.cat_id = p.cat_id JOIN
exp_channel_titles t ON p.entry_id = t.entry_id JOIN
exp_channel_data d ON t.entry_id = d.entry_id
WHERE
t.status = 'open' AND
d.site_id = 1 AND
d.channel_id = 3 AND
type.group_id = 2 AND
(p.cat_id = 7 AND ((p.cat_id = 175) OR (p.cat_id = 177))) AND
t.channel_id = 2
GROUP BY
type.cat_id
ORDER BY
type.cat_order
LIMIT 5"}
{if no_results}<li>Sorry, we don't have any events listed</li>{/if}
<li>{date} {title}</li>
{/exp:query}
</ul>But it’s not returning any results. If I do the same search in the cp (with Zenbu’s combined category search) I get seven results for one combination and three for the other, so either way there are entries to be listed. Unfortunately the way zenbu’s queries are built I can’t use the one that generates that list (as output in the ouput profiler)
Can anyone suggest a query that will achieve what I need?
Ok a few stupid errors leapt out at me when I posted that, so I have everything working except the tricky bit(!) Theis lineis the problem - I think I’m getting my and/pr login in a pickle:
(p.cat_id = 7 AND ((p.cat_id = 175) OR (p.cat_id = 177))) ANDHere’s the whole query:
{exp:query sql="SELECT
type.cat_id,
type.cat_name as cat_name,
type.cat_url_title,
type.group_id,
t.title as title,
t.status,
t.channel_id,
t.site_id,
d.field_id_32 as date
FROM
exp_categories type JOIN
exp_category_posts p ON type.cat_id = p.cat_id JOIN
exp_channel_titles t ON p.entry_id = t.entry_id JOIN
exp_channel_data d ON t.entry_id = d.entry_id
WHERE
t.status = 'open' AND
t.site_id = 1 AND
type.group_id = 2 AND
(p.cat_id = 7 AND ((p.cat_id = 175) OR (p.cat_id = 177))) AND
t.channel_id = 3
GROUP BY
type.cat_id
ORDER BY
type.cat_order
LIMIT 5"}I think I’ve posted this to the wrong group, this isn’t really tech support! Please could someone move it? (I don’t think I can)
Here’s a simpler way of looking at what I’m trying to do:
{exp:channel:entries channel="events" category="7&(175|177)"}I know that this syntax isn’t available, but how else could I achieve it?