在Doctrine DQL中选择count（），使用left join manyToMany单向关系，其中user不具有关系特定组

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.

标准ManyToMany来自FOSUserBundle（和SonataUserBundle）的用户和组实体之间的单向关系。系统：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.

问题非常类似于：如何在多对多关系中获取没有与DQL和Doctrine相对应的链接实体的实体？不同之处在于我只需要检查一个特定的组。

Working SQL (returns 1423):

工作SQL（返回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):

不正确地工作DQL（返回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:

问题是DQL生成不同的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?

有什么建议么？

2 个解决方案

#1

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

我不认为你的DQL是对的。你可以发布吗？但与此同时，这应该有效。

   $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 ;)

以这种方式编写DQL也更容易阅读;）

#2

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

我在DQL中设法做到的唯一方法是使用子查询：

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
)

#1