Select count() in Doctrine DQL with left join manyToMany unidirectional relation where user does NOT have relation specific group

Question

Situaction: I am trying to select count() in DQL for users NOT in specific group.

Standard ManyToMany unidirectional relation between User and Group entities from FOSUserBundle (and SonataUserBundle). System: Symfony 2.5, Doctrine 2.4.

Entity User

P.S. this is not real code copied. It is not possible because there are several layers extending with different config files in different formats and places, so if you spot mistype, this is not the problem.

namespace RAZ\UserBundle\Entity;
/**
 * @ORM\Table(name="fos_user_user")
 */
class User
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var Collection
     *
     * @ORM\ManyToMany(targetEntity="Group")
     * @ORM\JoinTable(name="fos_user_user_group")
     */
    protected $groups;
}

Entity Group

namespace RAZ\UserBundle\Entity;
/**
 * @ORM\Table(name="fos_user_group")
 */
class Group
{
/**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;
}

Question: can this even be done in DQL?

Question very similar to: How to get entities in a many-to-many relationship that do NOT have a corresponding linked entity with DQL and Doctrine? The difference is I need to check for only one specific group.

Working SQL (returns 1423):

SELECT COUNT(*) cnt
FROM fos_user_user u
LEFT JOIN fos_user_user_group dug ON u.id = dug.user_id AND dug.group_id = 70
WHERE dug.user_id IS NULL

Incorrectly working DQL (returns 3208):

SELECT COUNT(u)
FROM RAZUserBundle:User u
LEFT JOIN u.groups dug WITH dug.id = 70
WHERE dug IS NULL

Problem is DQL generates different SQL:

SELECT COUNT(u.id)
FROM fos_user_user u
LEFT JOIN fos_user_user_group ug ON u.id = ug.user_id
LEFT JOIN fos_user_group g ON g.id = ug.group_id AND (g.id = 70)
WHERE g.id IS NULL

Any suggestions?

I think you may have to define your ManyToMany intermediate table as a separate entity (connected to User and Group with OneToMany and ManyToOne relationships, and potentially with extra code in the entities to handle that). You can then use @Matthew A Thomas's suggestion, I think it's not working currently because you just don't have a UserGroup entity. — frumious, Sep 17 '14 at 12:02
It's also interesting that the generated SQL gives the wrong answer, have you identified why that is? Is it that the LEFT JOIN onto fos_user_user_group is returning a lot of nulls for Users with no groups at all? Only thing occurring to me right now is to do two queries, and subtract the number who *do* match from the total. — frumious, Sep 17 '14 at 12:07
So you are saying it is impossible select count in current implementation? — Aurelijus Rozenas, Sep 17 '14 at 12:15
I don't know for certain, but I'm not aware of a way. There's always raw SQL, if you don't want to alter your entities? — frumious, Sep 17 '14 at 12:17
I used native SQL where it was possible, but there are some big DQL queries in project that would extremely complicated to redo it in SQL. I still think it's odd I cannot select this... — Aurelijus Rozenas, Sep 17 '14 at 12:23

score 8 · Answer 1 · edited Jun 20 '15 at 17:40

8

I don't think your DQL is quite right. Can you post it? But in the meantime, this should work.

   $em = $this->getDoctrine()->getManager();
   $qb = $em->createQueryBuilder();

   $result = $qb->select('COUNT(u)')
                ->from('UserBundle:User' , 'u')
                ->leftJoin('u.UserGroup','g')
                ->where('g.GroupId = :id')
                ->andWhere('g.UserId = :null')
                ->setParameter('id', 70)
                ->setParameter('null', null)
                ->getQuery()
                ->getOneOrNullResult();

Also writing your DQL this way is easier to read ;)

edited Jun 20 '15 at 17:40

Lunfel

2,499
21
29

answered Sep 17 '14 at 09:41

Matthew A Thomas

924
7
15

Updated question to include more specific details and changed bidirectional to unidirectional (sorry, noticed that only now). – Aurelijus Rozenas Sep 17 '14 at 10:45
Your example is not working for me. There is not joining entity UserGroup. User has groups but joining table is only in sql. – Aurelijus Rozenas Sep 17 '14 at 10:53
You have updated your question since my response, please post entities – Matthew A Thomas Sep 17 '14 at 10:59
Updated question with entities. – Aurelijus Rozenas Sep 17 '14 at 11:42
@Aure, you might try `leftJoin('u.UserGroup','g', 'WITH', 'u.groupProperty= g.id')`, replace groupProperty with appropriate relation parameter. See http://stackoverflow.com/a/37685148/2883328 – Dennis Jun 08 '16 at 17:19

score 6 · Accepted Answer · answered Sep 19 '14 at 08:07

6

The only way I managed to do it in DQL is use subquery:

SELECT COUNT(u)
FROM RAZUserBundle:User u
WHERE u.id NOT IN (
    SELECT u2.id
    FROM RAZUserBundle:User u2
    JOIN u2.groups g WITH g.id = 70
)

answered Sep 19 '14 at 08:07

Aurelijus Rozenas

2,176
2
28
40

Select count() in Doctrine DQL with left join manyToMany unidirectional relation where user does NOT have relation specific group

Situaction: I am trying to select count() in DQL for users NOT in specific group.

Entity User

Entity Group

Question: can this even be done in DQL?

2 Answers2

Linked