Select count () in Doctrine DQL with the left union of a multiuser unidirectional relationship in which the user does NOT have a specific relationship group

Question

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

The standard multidimensional unidirectional relationship between User and Group objects from FOSUserBundle (and SonataUserBundle ). System: Symfony 2.5, Doctrine 2.4.

Custom object

PS is not a real code copied. This is not possible because there are several levels that expand with different configuration files in different formats and places, so if you notice an error, this is not a 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; } 

Group of objects

 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 be done even in DQL?

The question is very similar to: How do I get objects in a many-to-many relationship that do not have an associated object with DQL and Doctrine? The difference is, I need to check 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 

The problem is that 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?

Answer 1

The only way I managed to do this in DQL is to use a 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 ) 

Answer 2

I do not think your DQL is absolutely right. Can you post it? But at the same time it 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 is easier to read;)