查找所有子记录都具有给定值的所有父记录(而不只是某些子记录) [英] 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)Forall 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)

对于您的问题，它类似于:

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
)

即没有参与者的任何给定事件，因此状态！='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屋！