CODEWITHSUNDEEP
phpormpropel
Walt
Walt

Reputation: 1531

How do you do NOT IN or LEFT JOIN/IS NULL with Propel ORM?

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?

Upvotes: 1

Views: 2394

Answers (2)

Davide
Davide

Reputation: 66

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. :)

Upvotes: 2

v3rt1g0
v3rt1g0

Reputation: 43

Can you be more specific?

Otherwiste, use <> ?

Upvotes: 0

Related Questions