1

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?

Druckles
  • 3,161
  • 2
  • 41
  • 65

1 Answers1

2

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

bobs
  • 21,844
  • 12
  • 67
  • 78
  • Thank you. That was just what I was looking for. I hadn't considered a left join. – Druckles Jul 29 '11 at 18:42
  • I'd like to add, for anyone attempting a join with multiple criteria in Propel, see this link: http://stereointeractive.com/blog/2007/05/12/left-joins-with-multiple-conditions-using-propel-criteria/. – Druckles Jul 29 '11 at 18:55