将 Not In Sub-Select 重写为 Join for Propel

发布于 2024-11-27 04:09:24 字数 503 浏览 2 评论 0原文

给定以下模式：

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 不支持子选择。可以先执行子选择并将其直接作为数组传递，但我宁愿在一次调用中完成。我发现这篇文章，它建议使用自定义条件或将其转换为联接。

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

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

爱的那么颓废 2024-12-04 04:09:24

我认为这可能是返回的子查询行的替代

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 中

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

回复收藏 0 原文

~没有更多了~