CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
berkes
berkes

Reputation: 27553

With Has_many, how to return a list of objects without a certain related object?

My Event has_many :event_people, An EventPerson has, amongst others a field called "Role".

I want to find the query or WHERE to return a list of events without an EventPerson with role Moderator. 

 Event.join(:event_people).where("event_people.role <> 'moderator' ")

This results in the SQL:

 SELECT `events`.* FROM `events` INNER JOIN `event_people` ON `event_people`.`event_id` = `events`.`id` WHERE (event_people.event_role <> 'moderator' )

This returns a carthesian product (it returns a single Event multiple times, once for each relation), it will still return the events even when they "have no moderator". Since events can have event_people with roles like 'speaker' or 'coordinator'.

What would be the SQL for this? Bonuspoints for translating that to ActiveRecord.

Solution in ActiveRecord, after following the hint on having.

Event.select("events.*").joins(:event_people).group("event_people.id").having("sum(case when event_people.role = 'Moderator' then 1 else 0 end) = 0")

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I approach this type of query using aggregation and having:

select ep.id
from event_people ep
group by ep.id
having sum(case when ep.role = 'Moderator' then 1 else 0 end) = 0;

That is, count up the number of moderators on each event. Where there are none, return the event.

I don't know how to express this in activerecord.

Upvotes: 2

Related Questions