如何使用 Propel ORM 进行 NOT IN 或 LEFT JOIN/IS NULL？

发布于 2024-12-03 15:31:39 字数 265 浏览 0 评论 0原文

您好，我正在寻找数据库中不在另一个表中的行。如果外键行存在，我不希望表行包含在结果集中。

例如：

Group Table
Id - Title - Motto - Logo

Member Table
Id - FirstName - LastName

GroupMemberMap Table
Id - Group - Member

我想获取所有未分配到组的成员。

有人知道如何使用 Propel 做到这一点吗？

原文

Hi I am looking to select rows in my database that are not in another table. If the foreign keyed row exists I don't want the table row included in the result set.

For example:

Group Table
Id - Title - Motto - Logo

Member Table
Id - FirstName - LastName

GroupMemberMap Table
Id - Group - Member

I want to get all the members that are not assigned to a group.

Anyone know how to do that with Propel?

分享到QQ

分享到微博

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

发布评论

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

小瓶盖 2024-12-10 15:31:39

两个示例：

左连接

SELECT * FROM 成员 m LEFT JOIN member_group mg ON mg.member_id = m.id WHERE mg.member_id IS NULL

$c = new Criteria();
$c->addJoin(MemberPeer::ID, MemberGroupPeer::MEMBER_ID, Criteria::LEFT_JOIN);
$c->add(MemberGroupPeer::MEMBER_ID, NULL, Criteria::ISNULL);

return MemberPeer::doSelect($c);

子查询

SELECT * FROM member m WHERE id NOT IN (SELECT DISTINCT(member_id) FROM member_group)

$c = new Criteria();
$subQuery = sprintf('SELECT DISTINCT(%s) FROM %s', MemberGroupPeer::MEMBER_ID, MemberGroupPeer::TABLE_NAME);
$c->add(MemberPeer::ID, MemberPeer::ID . ' NOT IN (' . $subQuery . ')', Criteria::CUSTOM);

return MemberPeer::doSelect($c);

让我知道。 :)

Two examples:

Left join

SELECT * FROM member m LEFT JOIN member_group mg ON mg.member_id = m.id WHERE mg.member_id IS NULL

$c = new Criteria();
$c->addJoin(MemberPeer::ID, MemberGroupPeer::MEMBER_ID, Criteria::LEFT_JOIN);
$c->add(MemberGroupPeer::MEMBER_ID, NULL, Criteria::ISNULL);

return MemberPeer::doSelect($c);

Sub query

SELECT * FROM member m WHERE id NOT IN (SELECT DISTINCT(member_id) FROM member_group)

$c = new Criteria();
$subQuery = sprintf('SELECT DISTINCT(%s) FROM %s', MemberGroupPeer::MEMBER_ID, MemberGroupPeer::TABLE_NAME);
$c->add(MemberPeer::ID, MemberPeer::ID . ' NOT IN (' . $subQuery . ')', Criteria::CUSTOM);

return MemberPeer::doSelect($c);

Let me know. :)

回复收藏 0 原文