DQL查询与加入表和两个join [英] DQL query with joining table and two join

问题描述

我有2个实体：

/**
 * @ORM\Entity
 * @ORM\Table(name="users")
 */
class User
{
    /**
     * @ORM\ManyToMany(targetEntity="Myapp\UserBundle\Entity\Group")
     * @ORM\JoinTable(name="user_groups",
     *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="group_id", referencedColumnName="id")}
     * )
     */
    protected $groups;

    ...
}

和

/**
 * @ORM\Entity(repositoryClass="Myapp\UserBundle\Repository\GroupRepository")
 * @ORM\Table(name="groups")
 */
class Group
  ...

我无法找到一种方法来创建一个DQL查询，结果是这样的SQL：

I cant find a way to create a DQL query which results SQL like this:

SELECT g.name, g.id, count( u.id )
FROM users u
LEFT JOIN user_groups ug ON u.id = ug.user_id
RIGHT JOIN groups g ON g.id = ug.group_id
GROUP BY g.id

我尝试失败whith：

I tried and failed whith:

$this->getEntityManager()
    ->createQuery('
        SELECT g.id, g.name, count(u.id) as usercount FROM MyappUserBundle:User u
        JOIN u.groups g
        GROUP BY g.id'
    );

因为结果不包含没有用户的组。

since the result not contains the groups that has no user.

推荐答案

这是一个ManyToMany关系，不要事件试图加入关系表，只有相关的实体...
那么你是对的对于SQL查询，RIGHT JOIN ...但是Doctrine自动从FROM子句定义了接合类型。

It's a ManyToMany relation, don't event try to join on the relation table, only the related entity... Then, you were right with the RIGHT JOIN ... for a SQL query, but Doctrine automatically defines the jointure type from the FROM clause.

在DQL中，只有定义的关系由关节管理，所以你不需要USE或ON子句...

In DQL, only defined relations are managed by jointures, so you don't need USE or ON clauses...

这个是什么？

SELECT g.name, g.id, count( u.id )
FROM groups g
JOIN users u
GROUP BY g.id

这篇关于DQL查询与加入表和两个join的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！