How to have doctrine querybuilder with where not clause return values when null

Question

With Doctrine 2, I have the following query:

/**
 * @param Organization $organization
 * @param User $user
 * @return \Doctrine\ORM\QueryBuilder
 */
public function getFindByOrganizationQueryBuilder(Organization $organization, User $user)
{
    $builder = $this
        ->createQueryBuilder('u')
        ->leftJoin('u.roles', 'r')
        ->where($this->createQueryBuilder('u')->expr()->orX(
                $this->createQueryBuilder('u')->expr()->notIn('r.role', array('ROLE_SUPER_ADMIN', 'ROLE_ADMIN')),
                $this->createQueryBuilder('u')->expr()->isNull('r')
            ))
        ;

    return $builder;
}

This does not work as expected and returns users which have at least one role different than ROLE_ADMIN or ROLE_SUPER_ADMIN

Alternatively, I tried to replace the where clause by

->where('r.role <> 'ROLE_ADMIN')
->andWhere('r.role <> 'ROLE_SUPER_ADMIN')

Then it does not return users who have no roles.

How can I change the query so as it returns any user who has not either ROLE_ADMIN or ROLE_SUPER_ADMIN ?

Thanks a lot !

Answer 1

You need to combine your OR and AND statements:

/**
 * @param Organization $organization
 * @param User $user
 * @return \Doctrine\ORM\QueryBuilder
 */
public function getFindByOrganizationQueryBuilder(Organization $organization, User $user)
{
    $builder = $this->createQueryBuilder('u');
    $builder
        ->leftJoin('u.roles', 'r')
        ->where($builder->expr()->orX(
                $builder->expr()->andX(
                    $builder->expr()->neq('r.role', 'ROLE_SUPER_ADMIN'),
                    $builder->expr()->neq('r.role', 'ROLE_ADMIN')
                ),
                $builder->expr()->isNull('r')
            ))
        ;

    return $builder;
}

Answer 2

Found a way:

/**
 * @param Organization $organization
 * @param User $user
 * @return \Doctrine\ORM\QueryBuilder
 */
public function getFindByOrganizationQueryBuilder(Organization $organization, User $user)
{
    $nots = $this->_em
        ->createQueryBuilder()
        ->select('u.id')
        ->from('AppBundle:User\User', 'u', 'u.id')
        ->leftJoin('u.roles', 'r')
        ->where('r.role = ?1')
        ->orWhere('r.role = ?2')
        ->setParameter(1, 'ROLE_ADMIN')
        ->setParameter(2, 'ROLE_SUPER_ADMIN')
        ->getQuery()
        ->getResult()
        ;

    $nots = array_keys($nots);

    $builder = $this
        ->createQueryBuilder('u')
        ->leftJoin('u.roles', 'r')
        ->leftJoin('u.associates', 'a')
        ->leftJoin('a.organization', 'o')
        ->where('o = ?1')
        ->andWhere('u <> ?2')
        ->setParameter(1, $organization)
        ->setParameter(2, $user)
        ;

    if (!empty($nots))
    {
        $builder = $builder
            ->andWhere($this->createQueryBuilder('u')->expr()->notIn('u.id', $nots));
    }

    return $builder;
}