Doctrine query : confusing addWhere, andWhere, orWhere

Question

I have a query :

$q->andWhere($q->getRootAlias().'.is_published = ?', 1);
$q->andWhere($q->getRootAlias().'.published_at >= ?', time());
$q->leftJoin($q->getRootAlias().'.EventLdapGroup l');
$q->andWhereIn('l.ldap_group_id', $permissions_id );
$q->orWhere('l.ldap_group_id IS NULL);

which outputs :

FROM 
    Event r LEFT JOIN r.EventLdapGroup l 
WHERE 
    r.is_published = ? AND 
    r.published_at >= ? AND 
    l.ldap_group_id IN (?, ?) OR 
    l.ldap_group_id IS NULL

The only problem is that if ldap_group_id is null (the last condition) it'll remove the is_published and published_at conditions.

I want something like that [either the values are in ldap_group_id, or either it's null] :

FROM 
    Event r LEFT JOIN r.EventLdapGroup l 
WHERE 
    r.is_published = ? AND 
    r.published_at >= ? AND 
    (l.ldap_group_id IN (?, ?) OR l.ldap_group_id IS NULL)

And i must say that i'm lost with the complex where condition. How to achieve that ?

Thanks

Answer 1

Instead of your last two lines

$q->andWhereIn('l.ldap_group_id', $permissions_id );
$q->orWhere('l.ldap_group_id IS NULL);

Try this approach

$q->andWhere($qb->expr()->orx(
   $qb->expr()->in( 'l.ldap_group_id', $permissions_id ),
   $qb->expr()->isNull( 'l.ldap_group_id' );

Answer 2

Ok solution is here :

$q->andWhere('(l.ldap_group_id IN ( ' . implode(",", $permissions_id) . ') OR l.ldap_group_id IS NULL )' );