带有连接表和两个连接的 DQL 查询 [英] DQL query with joining table and two join

问题描述

我有 2 个实体:

/**
 * @ORMEntity
 * @ORMTable(name="users")
 */
class User
{
    /**
     * @ORMManyToMany(targetEntity="MyappUserBundleEntityGroup")
     * @ORMJoinTable(name="user_groups",
     *      joinColumns={@ORMJoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORMJoinColumn(name="group_id", referencedColumnName="id")}
     * )
     */
    protected $groups;

    ...
}

和

/**
 * @ORMEntity(repositoryClass="MyappUserBundleRepositoryGroupRepository")
 * @ORMTable(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

我尝试过但失败了:

$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 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！