使用Doctrine和Symfony2查询多对多关系

I'm triyng to understand how the many to many relationship works with Doctrine and Symfony2.

我很想知道多对多关系如何与Doctrine和Symfony2一起工作。

I've recreated the example shown in the official documentation (goo.gl/GYcVE0) and i have two Entity Classes: User and Group as you can see below.

我重新创建了官方文档（goo.gl/GYcVE0）中显示的示例，我有两个实体类：用户和组，如下所示。

<?php
/** @Entity **/
class User
{
    // ...

    /**
     * @ManyToMany(targetEntity="Group", inversedBy="users")
     * @JoinTable(name="users_groups")
     **/
    private $groups;

    public function __construct() {
        $this->groups = new \Doctrine\Common\Collections\ArrayCollection();
    }

    // ...
}

/** @Entity **/
class Group
{
    // ...
    /**
     * @ManyToMany(targetEntity="User", mappedBy="groups")
     **/
    private $users;

    public function __construct() {
        $this->users = new \Doctrine\Common\Collections\ArrayCollection();
    }

    // ...
}

If i update my DB i get this MySQL Schema:

如果我更新我的数据库，我得到这个MySQL架构：

CREATE TABLE User (
    id INT AUTO_INCREMENT NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB;
CREATE TABLE users_groups (
    user_id INT NOT NULL,
    group_id INT NOT NULL,
    PRIMARY KEY(user_id, group_id)
) ENGINE = InnoDB;
CREATE TABLE Group (
    id INT AUTO_INCREMENT NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB;
ALTER TABLE users_groups ADD FOREIGN KEY (user_id) REFERENCES User(id);
ALTER TABLE users_groups ADD FOREIGN KEY (group_id) REFERENCES Group(id);

The problem is that in Symfony2 I need the Entity to generate a query and in this case I don't have an Entity associated to the table users_group because this table is created automatically by the framework.

问题是在Symfony2中我需要实体来生成查询，在这种情况下，我没有与表users_group关联的实体，因为该表是由框架自动创建的。

So, how can I retrieve the information related to this relationship table? For example I need to get all the Users in a Group which are the users that have an id that appears in the table users_group.

那么，我如何检索与此关系表相关的信息？例如，我需要获取组中的所有用户，这些用户具有出现在表users_group中的id。

How can I do that using the DQL, QueryBuilder or other methods?

我如何使用DQL，QueryBuilder或其他方法来做到这一点？

Thanks a lot.

非常感谢。

1 个解决方案

#1

You can write a join DQL query as below

您可以编写一个连接DQL查询，如下所示

$em = $this->getContainer()->get('doctrine')->getManager();
$repository = $em->getRepository('YourNamespaceYourBundle:User');
$query = $repository->createQueryBuilder('u')
    ->innerJoin('u.groups', 'g')
    ->where('g.id = :group_id')
    ->setParameter('group_id', 5)
    ->getQuery()->getResult();

Your mapping for groups property in User entity will handle join part itself you don't have to mention the junction table in your DQL query

您在User实体中对groups属性的映射将处理连接部件本身，您不必在DQL查询中提及连接表

#1