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.

Lennart - Slava Ukraini · Accepted Answer · 2014-05-17T05:50:41.257

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

After trying both of these excellent examples, the first example returns all EVENT parents, even those without PARTICIPATION child records, as they would match the criteria. The second example returns only EVENT parent records with PARTICIPANT child records, but the COUNT(*) takes time to process. — sknight, Oct 19 '17 at 18:13

score 3 · Answer 2 · answered Oct 19 '17 at 18:30

I really like Lennarts examples

I made a simple modification to the first example which will only return EVENT parent records which have Participation Child records, and is much faster at processing than finding the counts for each.

SELECT e.* 
FROM events e
INNER JOIN participants p ON p.event_id = e.event_id
WHERE NOT EXISTS (
  SELECT 1 
  FROM PARTICIPANTS p
  WHERE p.status <> 'present'
  AND p.event_id = e.event_id
)
GROUP BY e.event_id

score 1 · Answer 3 · answered May 17 '14 at 03:45

1

What if you try to come at the query by finding the ids of the events where a person has a status other than "present" and then find all the unique event where that is the case?

unique_event_ids = Participant.where.not(status: "present").pluck(:event_id).uniq
events_you_want = Event.where(unique_event_ids)

answered May 17 '14 at 03:45

avalente1

246
1
5
21

1

`Participant.where.not(status: "present")` Will potentially grab a participant which has a peer with a status that is not equal to "present". This spoils the collections of ids. No event can have any child participant that is not equal to "present". – seder-mede May 17 '14 at 04:04

score 0 · Answer 4 · answered May 17 '14 at 04:32

You could use a subselect to filter out events that have participants that aren't present. It's probably not the most efficient way to do it, though.

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

I am getting 0 rows back from this, though it looks good in theory. Thanks. — seder-mede, May 17 '14 at 05:25

score 0 · Answer 5 · edited Oct 23 '18 at 13:50

I need a solution for this and the other answers didn't work for me, but here is my solution. I wrote two functions one to get the total number of child records and another to get the total number of child records that meet a particular condition in my case (true). Then I compared both functions. If the resulting arithmetic/evaluation equal zero it means all records meet the true criteria. Quite straightforward.

Select p.pid, p.Name, p.Group, udfn_TotalChildrenRecords(p.pid), udfn_TotalChildrenRecordsThatAreTrue(p.pid) 
From Parent AS p INNER JOIN Child AS c ON Parent.pid = child.pid
GROUP BY p.pid, p.Name, p.Group
HAVING udfn_TotalChildrenRecords(p.pid) - udfn_TotalChildrenRecordsThatAreTrue(p.pid) = 0

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

5 Answers5

Linked