Doctrine2 LEFT JOIN 有 2 个条件 [英] Doctrine2 LEFT JOIN with 2 conditions

问题描述

我正在尝试通过 ID 查找产品"，并在两个条件下加入所有照片":语言环境和活动状态.

这是我的 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)')-> 设置参数(数组('id' =>$id'地区' =>$this->getLocale(),'活跃' =>真的));

当没有照片或有活动照片时它可以正常工作，但当有非活动照片时就不行了，因为它不符合这两个条件之一.

如果我只使用一个条件，例如只使用语言环境部分，它工作正常:

<块引用>

$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)')-> 设置参数(数组('id' =>$id'地区' =>$this->getLocale()));

现在，我循环这些结果并取消设置所有不活动的照片......但我想要在 QueryBuilder 中做一个干净的方法.

我也尝试将条件放在 LEFT JOIN 子句上:

<块引用>

->leftJoin('photo.translations', 'phototTranslation', DoctrineORMQueryExprJOIN::WITH, 'photoTranslation.locale = :locale AND photoTranslation.active = :active')

但它总是返回照片，即使它处于非活动状态.

解决方案

对于这个问题的解决方案可能是:

$em = $this->getEntityManager();$qb = $em->createQueryBuilder();$qb->select('p', 'pp')->from('产品', 'p')->leftJoin('p.photos', 'pp')->leftJoin('pp.translations', 'ppt', DoctrineORMQueryExprJoin::WITH, $qb->expr()->andX($qb->expr()->eq('ppt.locale', ':locale'),$qb->expr()->eq('ppt.active', ':active')))->where('p.id', ':productId')-> 设置参数(大批('productId', $productId,'活跃'，$ 活跃，'locale', $locale));$query = $qb->getQuery();返回 $query->getResult();//或 ->getSingleResult();

注意:这个例子是在 Symfony2 (2.3) 实体仓库中实现的方法

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.

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

For now, I loop on theses results and unset all inactive Photos... but I'd like a clean way to do in the QueryBuilder.

I also tried to put the conditions on the LEFT JOIN clause :

->leftJoin('photo.translations', 'phototTranslation', DoctrineORMQueryExprJOIN::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', DoctrineORMQueryExprJoin::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();

NOTE: this example is the way to do it in Symfony2 (2.3) entity repository

这篇关于Doctrine2 LEFT JOIN 有 2 个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！