内部连接如何使用 Doctrine 和 Symfony2 处理多对多关系 [英] How does inner join work on a many-to-many relationship using Doctrine and Symfony2

问题描述

我最近解决了查询 ManyToMany 关系联接表的问题，解决方案与此 answer 并且想知道它是如何工作的.假设我在 groups 和 team 之间有一个简单的 ManyToMany 关系，会有一个 groups_team 表会自动在这里创建

I recently worked out an issue with querying ManyToMany relationship join tables, the solution was same as this answer and was wondering how it works. lets say i have a simple ManyToMany relationship between groups and team, there will be a groups_team tables that will automatically be created here

组实体

/**
 * Groups
 *
 * @ORMTable(name="groups")
 * @ORMEntity(repositoryClass="AppBundleModelRepositoryGroupsRepository")
 */
class Groups {

    /**
     * @ORMManyToMany(targetEntity="Team", inversedBy="group")
     */
    protected $team;

    public function __construct() {
        $this->team = new ArrayCollection();
    }

    /**
     * @var int
     *
     * @ORMColumn(name="id", type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORMColumn(name="groupname", type="string", length=255)
     */
    private $groupname;
    //obligatory getters and setters :)

团队实体

/**
 * Team
 * 
 * @ORMTable(name="team")
 * @ORMEntity(repositoryClass="AppBundleModelRepositoryTeamRepository")
 */
class Team {

    /**
     * @ORMManyToMany(targetEntity="Groups", mappedBy="team")
     */
    protected $group;

    public function __construct(){
        $this->group = new ArrayCollection();
    }

    /**
     * @var int
     *
     * @ORMColumn(name="id", type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORMColumn(name="teamname", type="string", length=255)
     */
    private $team;
    //[setters and getters here]

为了得到一个组中的所有团队，我必须查询 groups_team 表.我会直接在 mysql 中查询表，但在 symfony 中我必须这样做

in order to get all the teams in a group i would have to query the groups_team table.i would have directly queried the table in just mysql but in symfony i have to do this

      $groups = $em->getRepository("AppBundleModelEntityGroups")->findBy(array('tournament' => $tournament->getId()));

        //get all teams with group id in groups_team table
        foreach ($groups as $group) {
            $teamsingroup = $em->getRepository("AppBundleModelEntityTeam")->createQueryBuilder('o')
                    ->innerJoin('o.group', 't')
                    ->where('t.id = :group_id')
                    ->setParameter('group_id', $group->getId())
                    ->getQuery()->getResult();
            echo "</b>".$group->getGroupname()."</b></br>";
            foreach ($teamsingroup as $teamingroup) {
                echo $teamingroup->getTeam()."</br>";
            }
        }

谁能向我解释一下 innerJoin 是如何工作的，这背后的概念是什么，也许有一些文档可以了解这一点.有没有更好的方法来使用 symfony 和教义来做到这一点.

Can someone explain to me how the innerJoin is working and what is the concept behind this, maybe a few documentation to learn about this. are there better way to do this with symfony and doctrine.

推荐答案

在 2 个实体之间使用 ManyToMany 涉及在构建 DQL (教义查询)教义会根据您定义为注释的关系的性质自动加入联结表，因此请考虑您的查询

Using ManyToMany between 2 entities involves a third table generally called as a junction table in this type of relation when you build a DQL (doctrine query) doctrine automatically joins junction table depending on the nature of relation you have defined as annotation so considering your query

$teamsingroup = $em->getRepository("AppBundleModelEntityTeam")
                    ->createQueryBuilder('o')
                    ->innerJoin('o.group', 't')

您将在 innerJoin('o.group') 部分 o 中加入 Team 实体和 Group 实体是 Team 实体的别名，o.group 指的是在名为 group 的 Team 实体中定义的属性.

You are joining Team entity with Group entity in innerJoin('o.group') part o is the alias for Team entity and o.group refers to property defined in Team entity named as group.

/**
 * @ORMManyToMany(targetEntity="Groups", mappedBy="team")
 */
protected $group;

为这种类型的关系原则定义了一个 ManyToMany 注释首先将您的团队表与联结表连接起来，然后将您的联结表与组表连接起来，生成的 SQL 将类似于

Which has a ManyToMany annotation defined for this type of relation doctrine joins your team table first with junction table and then joins your junction table with groups table and the resultant SQL will be something like

SELECT t.*
FROM teams t
INNER JOIN junction_table jt ON(t.id = jt.team_id)
INNER JOIN groups g ON(g.id = jt.group_id)
WHERE g.id = @group_id

<小时>

另一件事与您为每个组获取团队的方式有关，一旦您将团队属性定义为 ArrayCollection 即$this->team = new ArrayCollection(); 在每个组对象上，您将通过在组对象上调用类似的 getTeam() 函数来获得与该特定组关联的团队集合到下面的代码.

---

Another thing related your way of getting team for each group you can minimize your code by excluding createQueryBuilder part within loop, once you have defined teams property as ArrayCollection i.e $this->team = new ArrayCollection(); on each group object you will get collections of teams associated to that particular group by calling getTeam() function on group object similar to below code.

foreach ($groups as $group) {
    $teamsingroup = $group->getTeam();
    echo "</b>".$group->getGroupname()."</b></br>";
    foreach ($teamsingroup as $teamingroup) {
        echo $teamingroup->getTeam()."</br>";
    }
}

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