具有2个条件的Doctrine2左联接 [英] Doctrine2 LEFT JOIN with 2 conditions
问题描述
我正在尝试通过ID查找产品",并在两种情况下将其所有照片"连接起来:区域设置和活动状态.
I'm trying to find a 'Product' by ID, and to left join all it's 'Photo' on two conditions: the locale AND the active state.
这是我的QueryBuilder:
Here's my QueryBuilder :
$queryBuilder = $this->createQueryBuilder('p')
->select('p, photos, photoTranslation')
->leftJoin('p.photos', 'photos')
->leftJoin('photos.translations', 'photoTranslation')
->where('p.id = :id')
->andWhere('(photoTranslation.locale = :locale OR photoTranslation.locale IS NULL)')
->andWhere('(photoTranslation.active = :active OR photoTranslation.active IS NULL)')
->setParameters(array(
'id' => $id
'locale' => $this->getLocale(),
'active' => true
));
在没有照片或有活动照片的情况下,它可以正常工作,但在没有活动照片的情况下,则不能正常工作,因为它不符合两个条件之一.
It works fine when there are no Photos or when there are ACTIVE photos, but not when there's an inactive Photo because it doesn't match one of the two conditions.
如果我仅使用一种条件,例如仅使用语言环境部分,它就可以正常工作:
If I use only one condition, for instance only the locale part, it works fine :
$queryBuilder = $this->createQueryBuilder('p')
->select('p, photos, photoTranslation')
->leftJoin('p.photos', 'photos')
->leftJoin('photos.translations', 'photoTranslation')
->where('p.id = :id')
->andWhere('(photoTranslation.locale = :locale OR photoTranslation.locale IS NULL)')
->setParameters(array(
'id' => $id
'locale' => $this->getLocale()
));
现在,我循环查看这些结果并取消设置所有不活动的照片...,但我想在QueryBuilder中使用一种干净的方法.
For now, I loop on theses results and unset all inactive Photos... but I'd like a clean way to do in the QueryBuilder.
我还尝试将条件放在LEFT JOIN子句上:
I also tried to put the conditions on the LEFT JOIN clause :
->leftJoin('photo.translations', 'phototTranslation', Doctrine\ORM\Query\Expr\JOIN::WITH, 'photoTranslation.locale = :locale AND photoTranslation.active = :active')
但是即使它不活动,它也总是返回照片.
But it always returns the Photo, even if it's inactive.
推荐答案
对此问题的解决方案可能是:
For this problem a solution may be:
$em = $this->getEntityManager();
$qb = $em->createQueryBuilder();
$qb
->select('p', 'pp')
->from('Product', 'p')
->leftJoin('p.photos', 'pp')
->leftJoin('pp.translations', 'ppt', Doctrine\ORM\Query\Expr\Join::WITH, $qb->expr()->andX(
$qb->expr()->eq('ppt.locale', ':locale'),
$qb->expr()->eq('ppt.active', ':active')
))
->where('p.id', ':productId')
->setParameters(
array(
'productId', $productId,
'active', $active,
'locale', $locale
)
);
$query = $qb->getQuery();
return $query->getResult(); // or ->getSingleResult();
注意:此示例是在Symfony2(2.3)实体存储库中执行此操作的方法
NOTE: this example is the way to do it in Symfony2 (2.3) entity repository
这篇关于具有2个条件的Doctrine2左联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!