Найти все родительские записи, где все дочерние записи имеют заданное значение (но не только некоторые дочерние записи)

Question

Событие имеет много участников. Участник имеет поле "статус".

class Event < ActiveRecord::Base
  has_many :participants
end

class Participant < ActiveRecord::Base
  belongs_to :event
end

Мне нужно найти все события, кроме следующих: события, где каждый из участников имеет статус "присутствует".

Я могу найти все события, где некоторые из его участников имеют статус "присутствует" со следующим кодом AR:

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

Это создает SQL как:

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

Это почти работает. Проблема в том, что если одна из строк участника (в рамках @participant.event_id) имеет статус чего-то другого, например, "прочь", событие все равно будет извлечено, потому что, по крайней мере, некоторые из записей одного уровня имеют статус, равный чему-то другому, нежели "присутствует".

Мне нужно убедиться, что я отфильтровываю каждую запись события со всеми участниками со статусом "настоящее".

Я открыт для ActiveRecord или решений SQL.

Answer 1

Если я правильно понимаю, ваша проблема может быть классифицирована как реляционное разделение. Есть два основных подхода к этому:

1a) Forall x: p(x)

который в SQL должен быть переведен на:

1b) НЕ существует x: НЕ p(x)

Для вашей проблемы это будет что-то вроде:

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

то есть любое данное событие, где не существует участника с таким статусом!= "присутствует"

Другой основной способ сделать это состоит в том, чтобы сравнить количество участников с количеством участников с имеющимся статусом

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 )

Оба решения не проверены, поэтому там могут быть ошибки, но это должно дать вам начало

Answer 2

Мне очень нравятся примеры Леннарта

Я сделал простую модификацию первого примера, которая будет возвращать только родительские записи СОБЫТИЯ, которые имеют записи об участии дочерних элементов, и намного быстрее обрабатывает, чем находит количество для каждой.

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

Answer 3

Что если вы попытаетесь прийти к запросу, найдя идентификаторы событий, в которых у человека есть статус, отличный от "настоящего", а затем найдете все уникальные события, где это имеет место?

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

Answer 4

Мне нужно решение для этого, и другие ответы не сработали для меня, но вот мое решение. Я написал две функции: одну для получения общего количества дочерних записей, а другую для получения общего количества дочерних записей, которые удовлетворяют определенному условию в моем случае (true). Затем я сравнил обе функции. Если полученная арифметика / оценка равна нулю, это означает, что все записи соответствуют истинным критериям. Довольно просто.

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

Answer 5

Вы можете использовать подвыбор, чтобы отфильтровать события, в которых есть участники, которых нет. Хотя, возможно, это не самый эффективный способ сделать это.

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