重写不在子选择中作为推进 [英] Rewriting Not In Sub-Select as Join for Propel

问题描述

给出以下架构:

person:
  id: ~
group:
  id: ~
group_membership:
  person_id: ~
  group_id: ~

我正在尝试使用Propel标准查找不在某个组中的成员，以下SQL会这样做:

SELECT * FROM person
WHERE id NOT IN (
  SELECT person_id FROM group_membership
  WHERE group_id = 1
);

很遗憾，Propel不支持子选择.可以先执行子选择，然后直接将其作为数组传递，但我宁愿在一个调用中进行选择.我发现本文，该文章建议使用自定义条件或将其转换为联接. /p>

是否可以将上述SQL转换为没有嵌套选择的单个Join?

解决方案

我认为这可以代替子查询

SELECT *
FROM person
LEFT OUTER JOIN group_membership
  ON person.id = group_membership.person_id
   AND group_id = 1
WHERE group_membership.person_id is null
;

在person_id为null的地方返回的行表示person中但在group_membership

中不存在的行

Given the following schema:

person:
  id: ~
group:
  id: ~
group_membership:
  person_id: ~
  group_id: ~

I am attempting to find members not within a certain group using Propel's Criteria, which the following SQL will do:

SELECT * FROM person
WHERE id NOT IN (
  SELECT person_id FROM group_membership
  WHERE group_id = 1
);

Unfortunately, Propel doesn't support sub-selects. It's possible to perform the sub-select first and pass it directly as an array, but I would rather do it in one call. I found this article, which suggests using a custom criteria or converting it to a join.

Is it possible to convert the above SQL to a single Join with no nested selects?

解决方案

I think this may be a substitute for the sub-query

SELECT *
FROM person
LEFT OUTER JOIN group_membership
  ON person.id = group_membership.person_id
   AND group_id = 1
WHERE group_membership.person_id is null
;

Rows returned where the person_id is null indicate where rows exist in person but not in group_membership

这篇关于重写不在子选择中作为推进的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！