教义querybuilder并非以leftJoin返回一对多关系中的所有记录 [英] Doctrine querybuilder returns not all records in one to many relation with leftJoin

问题描述

我有一个代表广告的广告实体.此广告实体与adRemark的关系过多.这样做的原因是adRemark由于支持多种语言而包含多个记录.

I have an Ad entity that represents an advertisement. This Ad entity has a one too many relation with adRemark. Reason to do this is that the adRemark contains multiple records because of multi language support.

一种广告只能在一种语言中使用一个adRemark，但会误记未填写的语言的记录，或者甚至由于没有语言数据而可能没有adRemark记录.

An Ad can have one only one adRemark per language but can mis records for language that are not filled in or can even have no adRemark record as no language data is filled in.

我正在建立一个查询，以检索包括adRemark在内的所有广告.

I'm building a query that retrieves all ads including the adRemark.

       $query = $this->createQueryBuilder('ad')
            ->select('ad.id, ad.title, ad.year, ad.hours, ad.status')
            ->addSelect('rem.remark')
            ->leftJoin('ad.remark', 'rem')    
            ->andWhere("rem.language = 'NL' or rem.language is null")
            ->getQuery()
            ->getResult();

通过此查询，我可以获取所有带有例如荷兰语(NL)备注或没有adRemark记录的广告.但是我想念没有NL adRemaks记录但有EN或DE记录的广告.

With this query i'm getting all ads that have for example the dutch (NL) remark filled in or no adRemark records. But i'm missing the ads the have for example no NL adRemaks record but do have an EN or DE record.

我工作了几个小时，但无法定义一个好的查询.非常感谢您的帮助.

I working this for hours but i'm not able to define a good query. Help is really appreciated.

Herby sql dump:

Herby the sql dump:

选择ad.id，ad.title，ad.year，ad.hours，ad.status，rem.remark FROMMtr \ Bundle \ Entity \ Ad LEFT JOIN ad.remark rem WHERE(rem.language ='NL'或rem.language为空)"

"SELECT ad.id, ad.title, ad.year, ad.hours, ad.status, rem.remark FROM Mtr\Bundle\Entity\Ad LEFT JOIN ad.remark rem WHERE (rem.language = 'NL' or rem.language is null)"

推荐答案

您不想过滤所有结果集，而只过滤联接中的 ，因此将条件移至加入子句.

You do not want to filter all the result set, but only on the join, so move the condition to the join clause.

将查询更改为:

use Doctrine\ORM\Query\Expr\Join;

$query = $this->createQueryBuilder('ad')
        ->select('ad.id, ad.title, ad.year, ad.hours, ad.status')
        ->addSelect('rem.remark')
        ->leftJoin('ad.remark', 'rem', Join::WITH, "rem.language = 'NL' OR rem.language is null")
        ->getQuery()
        ->getResult();

参考

• SQL联接:where子句与on子句
• 如何在Doctrine中进行左联接?

这篇关于教义querybuilder并非以leftJoin返回一对多关系中的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！