sql - find all parent records where all child records have a given value (but not just some child records)

Question

An event has many participants. A participant has a field of "status".

class Event < ActiveRecord::Base
  has_many :participants
end

class Participant < ActiveRecord::Base
  belongs_to :event
end

I need to find all events except the following ones: events where every one of its participants has a status of 'present'.

I can find all events where some of its participants have a status of 'present' with the following AR code:

Event.joins(:participants).where
 .not(participants: {status: 'present'})
  .select("events.id, count(*)")
   .group("participants.event_id")
    .having("count(*) > 0")

That creates SQL like:

SELECT events.id, participants.status as status, count(*) 
FROM `events` INNER JOIN `participants` 
ON `participants`.`event_id` = `events`.`id` 
WHERE (`participants`.`status` != 'present') 
GROUP BY participants.event_id HAVING count(*) > 0

This almost works. The problem is that if one of the participant's rows (within the scope of @participant.event_id) has a status of something other like "away", the event will still get fetched, because at least some of the sibling records are of a status equal to something other than "present".

I need to ensure that I am filtering out every event record with all participants of a status of "present".

I am open to ActiveRecord or SQL solutions.

Answer 1

If I get it right your problem can be classified as relational division. There are basically two ways to approach it:

1a) Forall x : p(x)

which in SQL has to be translated to:

1b) NOT Exists x : NOT p(x)

For your problem that would be something like:

SELECT e.* 
FROM events e
WHERE NOT EXISTS (
    SELECT 1 
    FROM PARTICIPANTS p
    WHERE p.status <> 'present'
      AND p.event_id = e.event_id
)

i.e. any given event where there does not exist a participant such that status != 'present'

The other principle way of doing it is to compare the number of participants with the number of participants with status present

SELECT e.id 
FROM events e
JOIN participants p 
    ON p.event_id = e.id 
GROUP BY e.event_id 
HAVING count(*) = count( CASE WHEN p.status = 'present' then 1 end )

Both solutions are untested so there might be errors in there, but it should give you a start