doctrine-ormdoctrinesymfony4doctrine-query

How to use conditionType 'ON' with Doctrine?


I have an issue with Doctrine concerning the condition ON. I have read here that:

Now in doctrine 2 I have never seen a situation where ON can be used. In fact using ON always ends up in an exception saying you should use WITH.

But I need to redefine the join conditions. Here is my query:

$qb = $this->createQueryBuilder('fu');
$qb
    ->addSelect('cra')
    ->innerJoin('fu.chatRoomAdmins', 'cra')
    ->where('cra.operator = :operatorId')
    ->setParameter('operatorId', $operatorId);
$foreignUsers = $qb->getQuery()->getResult();

And here is the (important part) generated sql query (I change the name of table to be more readable):

SELECT *
FROM   fu
       INNER JOIN cra
               ON fu.id = cra.operator_id
WHERE  ( cra.operator_id = 'an_id');

But I need to change the ON condition to this: ON fu.id = cra.operator_id OR cra.operator_id IS NULL

If I use the WITH condition like this :

$qb
//..
    ->innerJoin('fu.chatRoomAdmins', 'cra', 'WITH', 'cra.operator IS NULL')
//..
$foreignUsers = $qb->getQuery()->getResult();

It changes my query like this:

SELECT *
FROM   fu
       INNER JOIN cra
               ON fu.id = cra.operator_id 
                  AND ( cra.operator_id IS NULL ) 
WHERE  ( cra.operator_id = 'an_id' ); 

But I want this ON condition : ON fu.id = cra.operator_id OR ( cra.operator_id IS NULL )

That why I tried to replace WITH by ON in my queryBuilder :

$qb
//..
    ->innerJoin('fu.chatRoomAdmins', 'cra', 'ON', 'cra.operator IS NULL')
//..
$foreignUsers = $qb->getQuery()->getResult();

But now I got an error : Expected end of string, got 'ON' (i'm using symfony 4)

Do you know a way to have this ON condition : ON fu.id = cra.operator_id OR ( cra.operator_id IS NULL ) ?


Solution

  • I found a way to do it with leftJoin :

    $qb = $this->createQueryBuilder('fu');
    $qb
        ->addSelect('cra')
        ->leftJoin('fu.chatRoomAdmins', 'cra')
        ->where('cra.operator = :operatorId OR cra.operator IS NULL');
    $foreignUsers = $qb->getQuery()->getResult();
    

    which give this SQL:

    SELECT *
    FROM   fu 
           LEFT JOIN cra
                  ON fu.id = cra.operator_id 
    WHERE  ( cra.operator_id = 'an_id' 
              OR cra.operator_id IS NULL );