如何用ActiveRecord查询所有子Box均满足条件的Room？

嘿，我懂你现在的困扰——原来的查询只能找出至少有一个子Box符合语言条件的Room，但你需要的是所有子Box都满足的情况。别担心，这里有两种实用的方法可以实现你的需求：

方法一：使用NOT EXISTS排除不符合的Room #

这种思路是反过来想：找出那些不存在任何一个子Box不包含指定语言的Room，也就是所有子Box都包含目标语言的Room。

假设你的表结构是rooms（房间表，主键id）和boxes（子Box表，外键room_id关联房间，availables_locales是text[]类型的语言数组），目标语言是'en_US'，SQL可以这么写：

SELECT r.*
FROM rooms r
WHERE NOT EXISTS (
    SELECT 1
    FROM boxes b
    WHERE b.room_id = r.id
      -- 检查是否存在某个Box不包含目标语言
      AND NOT ('en_US' = ANY(b.availables_locales))
);

逻辑说明： #

• NOT EXISTS子查询会检查当前Room下有没有任何一个Box不包含'en_US'；
• 如果子查询返回空（也就是没有这样的Box），就说明这个Room的所有子Box都包含目标语言，会被筛选出来。

方法二：使用GROUP BY + HAVING统计符合条件的比例 #

另一种思路是统计每个Room的总子Box数，以及其中包含目标语言的子Box数，当两者相等时，就说明所有子Box都符合条件。

SQL示例：

SELECT r.*
FROM rooms r
JOIN boxes b ON r.id = b.room_id
GROUP BY r.id
HAVING COUNT(*) = COUNT(CASE WHEN 'en_US' = ANY(b.availables_locales) THEN 1 END);

逻辑说明： #

• COUNT(*)计算当前Room的总子Box数量；
• COUNT(CASE...)只统计包含目标语言的子Box数量；
• 当两个计数相等时，意味着所有子Box都满足条件，这个Room会被保留。

额外注意事项 #

如果你的availables_locales可能存在空数组的情况，需要提前明确业务规则：

• 如果空数组不算包含目标语言，那上面的两种方法都可以直接用；
• 如果空数组需要被视为“包含所有语言”，那需要调整判断条件，比如把NOT ('en_US' = ANY(b.availables_locales))改成b.availables_locales IS NOT NULL AND NOT ('en_US' = ANY(b.availables_locales))，避免空数组被误判为不符合。

内容的提问来源于stack exchange，提问作者Benoit