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

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

我最近解决了查询ManyToMany关系连接表的问题,解决方案与此答案相同,并且想知道它是如何工作的。假设我在组和团队之间有一个简单的ManyToMany关系,将会有一个在这里自动创建的groups_team表

groups entity

/**
 * Groups
 *
 * @ORM\Table(name="groups")
 * @ORM\Entity(repositoryClass="AppBundle\Model\Repository\GroupsRepository")
 */
class Groups {

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

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

    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

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

team entity

/**
 * Team
 * 
 * @ORM\Table(name="team")
 * @ORM\Entity(repositoryClass="AppBundle\Model\Repository\TeamRepository")
 */
class Team {

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

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

    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

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

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_team table.i将直接查询mysql中的表,但在symfony我必须这样做

      $groups = $em->getRepository("AppBundle\Model\Entity\Groups")->findBy(array('tournament' => $tournament->getId()));

        //get all teams with group id in groups_team table
        foreach ($groups as $group) {
            $teamsingroup = $em->getRepository("AppBundle\Model\Entity\Team")->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>";
            }
        }

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.

有人可以向我解释一下innerJoin是如何工作的,这背后的概念是什么,可能需要一些文档来了解这一点。使用symfony和doctrine有更好的方法吗?

2 个解决方案

#1

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

在构建DQL(学说查询)原则时,在构造DQL(学说查询)原则时,在两个实体之间使用ManyToMany涉及通常称为联结表的第三个表,这取决于您定义为注释的关系的性质,因此请考虑您的查询

$teamsingroup = $em->getRepository("AppBundle\Model\Entity\Team")
                    ->createQueryBuilder('o')
                    ->innerJoin('o.group', 't')

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.

您正在使用innerJoin('o.group')中的Group实体加入Team实体,o是Team实体的别名,o.group是指Team实体中定义的名为group的属性。

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

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

其中为此类关系定义了一个ManyToMany注释,并且首先使用联结表将您的联合表连接到您的联合表,然后将您的联结表与组表连接,结果SQL将类似于

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

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.

另一个与你为每个组获得团队相关的方法是,你可以通过在循环中排除createQueryBuilder部分来最小化代码,一旦你将团队属性定义为ArrayCollection,即$ this-> team = new ArrayCollection();在每个组对象上,您将通过调用组对象上的getTeam()函数获得与该特定组关联的团队集合,类似于下面的代码。

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

2 个解决方案

#1

更多相关文章

随机推荐