在使用querybuilder的语言字段上，优先原则左联接在语言字段上 [英] Doctrine left join with priority on language field with querybuilder

问题描述

我正在使用以下查询:

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'")
    ->getQuery()
    ->getResult();

此查询工作正常，并以荷兰语返回广告的备注.该广告与备注具有一对多关系.

This query is working fine and returns the remark of a ad in the Dutch language. The ad has a one-to-many relation with its remark.

只有我也有带有英文备注而不是荷兰语的广告.我想得到该评论的英文说明，而列表中的其他评论仍是荷兰语.因此，也要总结一下对返回的语言的优先级列表吗?

Only I also have ads that have for example an English remark and not a Dutch one. The I will like to get the English remark of that one and on the others in the list still the Dutch remark. So too summarise making a priority list on the languages that are returned?

推荐答案

解决此问题的一种方法是使用附加

One way to solve this is to use an extra join without relation:

$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 = 'EN'")
    ->leftJoin(Remark::class, 'customRem', Join::WITH, 
    "rem.id <> customRem.id 
    AND rem.ad = customRem.ad 
    AND customRem.language = 'NL'")
    ->where('customRem.id IS NULL')
    ->getQuery()
    ->getResult();

这个想法是

• 如果广告存在NL语言注释，请将此注释添加到该广告期望的每个结果行中(本身为空)
• 如果不存在NL语言注释，而EN则存在，则连接的行将为空

最后，条件customRem.id IS NULL使此工作正常.

Finally, the condition customRem.id IS NULL makes this work.

多语言解决方案

对于3种受支持的语言，由于 DE> EN> NL ，您可以执行以下操作:

In the case of 3 supported languages, because DE > EN > NL, you could do:

->leftJoin(Remark::class, 'customRem', Join::WITH,        
     "rem.id <> customRem.id AND rem.ad = 
      customRem.ad AND rem.language < customRem.language")

对于多种语言，并假设具有"自定义"订购语言的能力，则可以使用:

For multiple languages and suppose a "customized" ability to order the languages, you could use:

"rem.id <> customRem.id 
AND rem.ad = customRem.ad AND 
(case when rem.language = 'NL' THEN 3 " .
"when rem.language = 'EN' THEN 2 " .
"when rem.language = 'DE' THEN 1 ELSE 0 END) < (case when customRem.language = 'NL' THEN 3 " .
"when customRem.language = 'EN' THEN 2 " .
"when customRem.language = 'DE' THEN 1 ELSE 0 END)"

或者，您可以创建"lang_position"表(ID，语言，位置)，并进行两次联接以从语言中获取位置.

Alternatively, you could create "lang_position" table(id, language, position) and join twice to get the position from the language.

这篇关于在使用querybuilder的语言字段上，优先原则左联接在语言字段上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！