在Doctrine DQL中选择count(),其中左连接manyToMany单向关系,用户没有关系特定组 [英] Select count() in Doctrine DQL with left join manyToMany unidirectional relation where user does NOT have relation specific group
问题描述
标准许多多个单向关系 User
和组实体从
FOSUserBundle
(和 SonataUserBundle
)。系统:Symfony 2.5,Doctrine 2.4。
Standard ManyToMany unidirectional relation between User
and Group
entities from FOSUserBundle
(and SonataUserBundle
). System: Symfony 2.5, Doctrine 2.4.
这不是真正的代码复制。这是不可能的,因为有不同的配置文件扩展不同的格式和地方,所以如果你发现错误类型,这不是问题。
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;
}
问题:甚至可以在DQL中完成?
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.
工作SQL(返回1423):
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
错误地使用DQL(返回3208):
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
问题是DQL生成不同的SQL:
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
任何建议? p>
Any suggestions?
推荐答案
我在DQL中设法完成的唯一方法是使用子查询:
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
)
这篇关于在Doctrine DQL中选择count(),其中左连接manyToMany单向关系,用户没有关系特定组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!