在Propel 1.5中执行具有多个条件的联接 [英] Performing Join with Multiple Criteria in Propel 1.5

问题描述

此问题来自此处和此处.

我最近已升级到Propel 1.5，并已开始在Criteria上使用它的查询功能.我有一个无法翻译的查询-具有多个条件的左连接:

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);

我考虑对该组中的所有人执行查询，获取主键并在数组上添加NOT IN，但是从查找中获取主键似乎并不特别简单，看起来不太优雅.

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.

关于 codenugget.org 的文章详细介绍了如何添加其他内容我尝试加入的条件:

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();

不幸的是，"useGroupMembershipQuery"会覆盖左连接.为了解决这个问题，我尝试了以下代码:

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

有人知道为什么这样做或在Propel 1.5中如何成功执行此联接而不必再次求助于条件吗?

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支持使用 addJoinCondition() .如果您更新Symfony插件，或转到 sfPropelORMPlugin ，则可以利用它.然后可以这样编写查询:

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');

这篇关于在Propel 1.5中执行具有多个条件的联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！