This question follows on from the questions here and here.
I have recently upgraded to Propel 1.5, and have started using it's Query features over Criteria. I have a query I cannot translate, however - a left join with multiple criteria:
SELECT * FROM person
LEFT JOIN group_membership ON
person.id = group_membership.person_id
AND group_id = 1
WHERE group_membership.person_id is null;
Its aim is to find all people not in the specified group. Previously I was using the following code to accomplish this:
$criteria->addJoin(array(
self::ID,
GroupMembershipPeer::GROUP_ID,
), array(
GroupMembershipPeer::PERSON_ID,
$group_id,
),
Criteria::LEFT_JOIN);
$criteria->add(GroupMembershipPeer::PERSON_ID, null, Criteria::EQUAL);
I considered performing a query for all people in that group, getting the primary keys and adding a NOT IN
on the array, but there didn't seem a particularly easy way to get the primary keys from a find, and it didn't seem very elegant.
An article on codenugget.org details how to add extra criteria to a join, which I attempted:
$result = $this->leftJoin('GroupMembership');
$result->getJoin('GroupMembership')
->addCondition(GroupMembershipPeer::GROUP_ID, $group->getId());
return $result
->useGroupMembershipQuery()
->filterByPersonId(null)
->endUse();
Unfortunately, the 'useGroupMembershipQuery' overrides the left join. To solve this, I tried the following code:
$result = $this
->useGroupMembershipQuery('GroupMembership', Criteria::LEFT_JOIN)
->filterByPersonId(null)
->endUse();
$result->getJoin('GroupMembership')
->addCondition(GroupMembershipPeer::GROUP_ID, $group->getId());
return $tmp;
For some reason this results in a cross join being performed for some reason:
SELECT * FROM `person`
CROSS JOIN `group_membership`
LEFT JOIN group_membership GroupMembership ON
(person.ID=GroupMembership.PERSON_ID
AND group_membership.GROUP_ID=3)
WHERE group_membership.PERSON_ID IS NULL
Does anyone know why this might be doing this, or how one might perform this join successfully in Propel 1.5, without having to resort to Criteria, again?
Propel 1.6 supports multiple criteria on joins with addJoinCondition()
. If you update the Symfony plugin, or move to sfPropelORMPlugin, you can take advantage of that. The query can then be written like this:
return $this
->leftJoin('GroupMembership')
->addJoinCondition('GroupMembership', 'GroupMembership.GroupId = ?', $group->getId())
->where('GroupMembership.PersonId IS NULL');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With