0

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.

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.

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

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 the sql dump:

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

Tom
  • 1,547
  • 7
  • 27
  • 50
  • Did you tried an `innerJoin` instead? Or does this result in the same problem? – Fabian Schmick Apr 08 '19 at 14:34
  • I have tried, but that results in the same problem indeed. – Tom Apr 08 '19 at 14:35
  • Could you edit your question with `var_dump($query->getSQL())` – Fabian Schmick Apr 08 '19 at 14:38
  • The dump is added – Tom Apr 08 '19 at 14:46
  • 2
    Please in code questions give a [mcve]--cut & paste & runnable code plus desired output plus clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS Re "But i'm missing the ads the have for example no NL adRemaks record but do have an EN or DE record." But you are asking for language NL or NULL. You also don't clearly tell us what rows you want; you just give wrong code & one way it's wrong. Do you need "or rem.adRemark is null" instead of "or rem.language is null"? – philipxy Apr 08 '19 at 16:00
  • I agree with philipxy. So far, I only can imagine you want to prefer the NL remarks, but your query limits it to the NL remarks (and to those remarks, where the language is set to NULL, which I assume is impossible). thus excluding the remarks from other languages (and lacking the NL remark, their associated ads as well). – Jakumi Apr 08 '19 at 18:41
  • Correct I prefer the NL remarks, but now I'm missing the ads that don't have an NL remark but have an other remark like EN or DE. The remark on these missing ads can have the value NULL that is no problem, but the compleet ad is missing from the query. – Tom Apr 08 '19 at 20:57

1 Answers1

1

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

Change the query to:

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

References

Jannes Botis
  • 11,154
  • 3
  • 21
  • 39
  • Getting the NL language works! Is it also posible to in the conditional Join to get the EN language only if the NL language does not exits else the NL language is returned? – Tom Apr 16 '19 at 10:11
  • @Tom Good question, I bet there are several ways to do this. Please if you do not mind, create a new question about it and I will certainly try to help. – Jannes Botis Apr 16 '19 at 17:08
  • I added a new question, hope you can help.. https://stackoverflow.com/questions/55715353/doctrine-left-join-with-priority-on-field-with-querybuilder – Tom Apr 16 '19 at 19:26