内连接如何使用 Doctrine 和 Symfony2 处理多对多关系

2022-01-03 00:00:00 php mysql symfony doctrine doctrine-orm

我最近解决了查询 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')

您正在加入 Team 实体和 Group 实体在 innerJoin('o.group') 部分 o是 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

<小时>

另一件事与您为每个组获取团队的方式有关，您可以通过在循环中排除 createQueryBuilder 部分来最小化代码，一旦您将团队属性定义为 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>";
    }
}

相关文章