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