Doctrine2 get object without relations

Question

An user has one role. A role has zero or many users.

I would like to find roles without users.

I need to have this query without using IN or NOT IN

I tried with join:

$qb = $this->createQueryBuilder('role');
$qb
    ->leftJoin('role.users', 'users')
    ->where('users IS NULL')

without join

$qb = $this->createQueryBuilder('role');
$qb
    ->where('role.users IS NULL')

with id:

$qb = $this->createQueryBuilder('role');
$qb
    ->leftJoin('role.users', 'users')
    ->where('users.role != role')

Do you have other ideas? Do I have no other choices than to use IN / NOT IN queries?

Thanks in advance

M Khalid Junaid · Accepted Answer · 2015-04-28T14:20:43.993

2

You can find roles that don't have any users by using a count query

$qb = $this->createQueryBuilder('role');
$qb ->addSelect('COUNT(users.id) AS total_users')
    ->leftJoin('role.users', 'users')
    ->groupBy('role.id')
    ->having('total_users = 0')
    ->getQuery()->getResult();

edited Apr 28 '15 at 14:20

answered Apr 28 '15 at 14:09

M Khalid Junaid

63,861
10
90
118

I'm having Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected. Any idea why? – goto Apr 28 '15 at 14:16
@goto change count to `COUNT(users.id)` – M Khalid Junaid Apr 28 '15 at 14:21
thanks, but do you know how to avoid the 8250 queries done by doctrine :O – goto Apr 28 '15 at 14:25
@goto i don't have any idea regarding 8250 queries and i guess above query will be executed as one query not for each role for for each user individually – M Khalid Junaid Apr 28 '15 at 14:30
1

Ok, thanks anyway! i will look more information about the increase of queries – goto Apr 28 '15 at 14:32

Doctrine2 get object without relations

1 Answers1

Linked