sql - Rails 事件记录查询 : Join on Child Condition to include only matching results

Question

我有两个模型 - Publisher 和 Policy。政策属于发布者，发布者有很多政策。在政策模型中，有一个名为“ad_partner”的列，我正在寻找没有包含广告合作伙伴中特定值的政策的发布商。最终目标是发布商列表，这些发布商没有任何与特定广告合作伙伴相关联的政策。

这是我当前的查询:

test = Publisher.joins(:policies).select('publishers.name, 
publishers.id, publishers.domain').where.not("policies.ad_partner ILIKE 
?", 'indexexchange.com').group("publishers.id, 
publishers.name").limit(10)

输出是这样的:

 +----+------------------+---------------------+
| id | name             | domain              |
 +----+------------------+---------------------+
| 1  | Business Insider | businessinsider.com |
| 2  | Wired            | wired.com           |
| 3  | CNET             | cnet.com            |
| 4  | Weather Channel  | weather.com         |
| 5  | Time Inc         | time.com            |
| 6  | IGN              | ign.com             |
| 7  | CBS              | cbs.com             |
| 8  | NBC              | nbc.com             |
| 9  | TMZ              | tmz.com             |
| 10 | HGTV             | hgtv.com            |
+----+------------------+---------------------+

这是不正确的，因为我知道大多数(如果不是全部)这些发布商的政策都包含 indexexchange.com 作为 ad_partner 中的一个值。我怀疑正在发生的事情是查询说“好的，我们正在从 JOIN 列表中删除策略，并输出按发布者分组的剩余策略，而实际上我想查找具有此条件的发布者，而不是创建条件作为它似乎在做。我觉得这里有一个明显的错误，但我一直在努力寻找它。有什么想法吗？

Answer 1

您正在寻找:

publishers who [do] not have any policies associated with a specific ad_partner

所以就这么说吧。首先找到有此类政策的发布商:

Policy.where('ad_partner ilike ?', partner).select(:publisher_id)

然后找到不在该列表中的发布者:

Publisher.where.not(
  id: Policy.select(:publisher_id).where('ad_partner ilike ?', partner)
)

在 Policy 查询中包含 select(:publisher_id) 将告诉 ActiveRecord 使用子查询，因此结果将是这样的 SQL:

select *
from publisher
where id not in (
  select publisher_id
  from policies
  where ad_partner ilike 'indexexchange.com'
)

这将为您提供没有重复的发布商。然后您可以添加所需的任何顺序和限制。

您的原始查询无法按您希望的方式工作，因为 JOIN 将生成包含所有内容的行:

publisher1_columns, policy_with_partner
publisher1_columns, policy_without_partner
publisher1_columns, policy_without_partner
publisher2_columns, policy_without_partner
...

然后您的 WHERE 将仅排除 JOIN 中的第一行，留下您不想要的两个“publisher1”行。

---

说 ilike 'indexexchange.com' 只是一种不区分大小写的复杂方式。如果你想要模式匹配，那么你会说:

where('ad_partner ilike ?', "%#{partner}%")

相反。