Doctrine left join with priority on language field

Question

I'm using the query below:

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?

Answer 1

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

The idea is

• if NL language remark exists for an ad, add this remark joined to each result row of this ad expect to itself(will be null)
• if NL language remark does not exist and an EN exists, then the joined row will be null

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

Multiple languages solution

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

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