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 !

Michael Sivolobov · Accepted Answer · 2015-03-21 20:25:31Z

2

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;
}

answered Mar 21, 2015 at 20:25

Michael Sivolobov

13.9k5 gold badges48 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sébastien Over a year ago

thanks but if the user has for instance ROLE_ADMIN and ROLE_USER, he will be returned. I want the user to not be included in the results if he has ROLE_ADMIN.

Sébastien Over a year ago

I also had to set parameters instead of using a plain text comparison

Sébastien · Accepted Answer · 2015-03-21 17:30:04Z

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;
}

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related