查找所有父记录，其中所有子记录都具有给定值（而不仅仅是某些子记录）

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 范围内）中有某个状态不是“present”而是“away”，那么事件仍然会被获取，因为至少一些兄弟记录的状态等于某个不是“present”的东西。

我需要确保过滤掉每个参与者状态都为“present”的所有事件记录。

我可以接受ActiveRecord或SQL的解决方案。

- seder-mede

Answer 1

如果我理解正确，您的问题可以归类为关系除法。基本上有两种方法可以解决它：

1a) 对于所有 x：p(x)

在SQL中必须翻译为：

1b) 不存在 x: NOT 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
)

即任何给定事件中，不存在状态不等于“present”的参与者。

另一种基本方法是将参与者数量与状态为“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 )

这两个解决方案都没有经过测试，所以可能存在错误，但它们应该会给你一个开始的方向。

- Lennart - Slava Ukraini

---

在尝试了这两个优秀的示例之后，第一个示例返回所有的EVENT父记录，即使它们没有PARTICIPATION子记录也会匹配标准。第二个示例仅返回具有PARTICIPANT子记录的EVENT父记录，但COUNT(*)需要时间来处理。 - sknight

Answer 2

我非常喜欢Lennart的例子

我对第一个例子进行了简单修改，它只返回有参与子记录的事件父记录，并且处理速度比每个计数要快得多。

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

- sknight

Answer 3

如果您尝试通过查找人员状态不是“present”的事件的ID，然后找到所有唯一的事件来查询，会怎样呢？

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

- avalente1

---

参与者.其中不(status: "present")。可能会获取一个具有状态不等于 “present” 的同行参与者。这破坏了ID的集合。任何事件都不能有任何子参与者不等于“present”。 - seder-mede

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

- Ted

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

- James Mason

---

我从这里得到了0行，尽管理论上看起来很好。谢谢。 - seder-mede