How to use an expression in select count(...)? (Doctrine)

Question

I have User and UserGroup entities. Each user has a group (UserGroup), and each user has a boss (User).

I would like to get a list of groups, and decide if the current user is the boss of someone in the group. I already implemented this in SQL, and now I tried to solve it using Doctrine. This is what I tried:

$qb = em()->createQueryBuilder()
    ->select(array('gr.id AS group_id', 'gr.name AS group_name', 'COUNT(us.boss = :current_user_id)>0 AS is_boss'))
    ->from('\App\Entity\UserGroup', 'ug')
    ->leftJoin('\App\Entity\User', 'us', 'WITH', 'us.group = ug.id')

    ->setParameter('current_user_id', $_SESSION['uid'])

    ->groupby('gr.id')
;

$groups = $qb->getQuery()->getScalarResult();

Unfortunately I get an uncaught QueryException, and I have no idea how to fix that. How is it possible to put an expression inside the COUNT(...) function?

What is the error message? And you should `groupBy` with all columns, not only id. — Felippe Duarte, Nov 26 '18 at 19:37
It works without the `COUNT(...)` part. The error message just contains the DQL query: `SELECT gr.id AS group_id, gr.name AS group_name, COUNT(us.boss = :current_user_id)>0 AS is_boss FROM \App\Entity\UserGroup ug LEFT JOIN \App\Entity\User us WITH us.group = ug.id GROUP BY gr.id` — Iter Ator, Nov 26 '18 at 19:45

score 1 · Accepted Answer · answered Nov 27 '18 at 22:32

1

You can use Mysql case function to do this:

$queryBuilder->addSelect('case when COUNT(case when us.boss = :current_user_id then 1 else 0 end)>0 then 1 else 0 end AS is_boss');

Reference

Is it possible to specify condition in Count()?

answered Nov 27 '18 at 22:32

Jannes Botis

11,154
3
21
39

How to use an expression in select count(...)? (Doctrine)

1 Answers1