Compare a count of subquery with scalar

Question

I'm using Symfony 4.3 and I have a problem in my DQL query.

The purpose of my query is to select email of user how have list of right for this is my DQL query:

        $qb = $this->createQueryBuilder('u')
        ->select('u.email,u.id')
        ->leftJoin('u.profile', 'profile')
        ->leftJoin('u.country', 'country')
        ->leftJoin('profile.privileges', 'pri')
        ->leftJoin('pri.ressource', 'resource')
        $checkRightQuery = $this->em->createQueryBuilder()
            ->select('count(rc)')
            ->from(Ressource::class, 'rc')
            ->leftJoin('rc.privileges', 'privil')
            ->leftJoin('privil.profile', 'prof')
            ->leftJoin('prof.user', 'user')
            ->where( $this->em->getExpressionBuilder()->in('rc.actionFront', ':rights'))
            ->andWhere('user.id =u.id');
        $qb->andWhere(
            $this->em->getExpressionBuilder()->eq(count($rights),
                $checkRightQuery
            )
        );

    $qb->setParameters(['rights' => $rights]);

The problem is when I take the result of the count it's not a scalar and it can't compare it to scalar.

Any help please?

does your database complain it's no scalar, or is it doctrine? According to some SO questions/answers, you *might* have to use `$checkRightQuery->getDql()` — Jakumi, Feb 04 '20 at 10:01
The doctrine compile it .The problem is that the subquery retun array how contatine a scalar value and when try to compare it to a scalar i have this error : [Syntax Error] line 0, col 675: Error: Expected Literal, got 'SELECT' — tawfik medhaffar, Feb 04 '20 at 10:09
Try using parethensis, as here https://stackoverflow.com/questions/53608128/doctrine-return-error-with-eq-no-with-in/53611606#53611606 — Jannes Botis, Feb 04 '20 at 11:58

score 1 · Accepted Answer · answered Feb 12 '20 at 16:56

Try using parenthesis on your condition with the subquery:

$qb->andWhere(
        $this->em->getExpressionBuilder()->eq(count($rights),
            '(' . $checkRightQuery . ')'
        )
    );

References

Doctrine return error with “eq”, no with “in”

Compare a count of subquery with scalar

1 Answers1