使用 Doctrine 和 Symfony2 查询多对多关系 [英] Query on a many-to-many relationship using Doctrine with Symfony2

问题描述

我正在尝试了解多对多关系如何与 Doctrine 和 Symfony2 一起工作.

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

我重新创建了官方文档 (goo.gl/GYcVE0) 中显示的示例，并且我有两个实体类:User 和 Group，如下所示.

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.

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

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

    public function __construct() {
        $this->groups = new DoctrineCommonCollectionsArrayCollection();
    }

    // ...
}

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

    public function __construct() {
        $this->users = new DoctrineCommonCollectionsArrayCollection();
    }

    // ...
}

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

If i update my DB i get this MySQL Schema:

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

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

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.

那么，如何检索与此关系表相关的信息?例如，我需要获取组中的所有用户，这些用户的 ID 出现在 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.

如何使用 DQL、QueryBuilder 或其他方法来做到这一点?

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

非常感谢.

推荐答案

你可以写一个 join DQL 查询如下

You can write a join DQL query as below

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

User 实体中 groups 属性的映射将自行处理连接部分，您不必在 DQL 查询中提及连接表

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

这篇关于使用 Doctrine 和 Symfony2 查询多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！