查找所有子记录都具有给定值的所有父记录(但不仅仅是一些子记录) [英] find all parent records where all child records have a given value (but not just some child records)

问题描述

一个活动有很多参与者.参与者有一个状态"字段.

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'.

我可以使用以下 AR 代码找到其中一些参与者处于出席"状态的所有事件:

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")

创建 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 范围内)的状态为离开"之类的其他状态，事件仍将被获取，因为至少有一些同级记录的状态等于当前"以外的其他内容.

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".

我对 ActiveRecord 或 SQL 解决方案持开放态度.

I am open to ActiveRecord or SQL solutions.

推荐答案

如果我猜对了，您的问题可以归类为关系除法.基本上有两种方法可以解决它:

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

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

1a) Forall x : p(x)

在 SQL 中必须转换为:

which in SQL has to be translated to:

1b) 不存在 x : 不存在 p(x)

1b) NOT Exists 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'

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

这篇关于查找所有子记录都具有给定值的所有父记录(但不仅仅是一些子记录)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！