How does inner join work on a many-to-many relatio

Question

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 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 = $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.

Answer 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

$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.

/**
 * @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

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.

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

Answer 2

I guess it's literally select statement with INNER JOIN using key columns defined entity class as mappedBy or inversedBy. Why don't you have a look of doctrine log and see what the native sql is composed?

How to get Doctrine to log queries in Symfony2 (stackoverflow)

http://vvv.tobiassjosten.net/symfony/logging-doctrine-queries-in-symfony2/ (some code examples)

I don't know your user story behind this, but I also heard that it is recommended to use one to many relationship instead of many to many, unless there is a strong reason to do so, as most of cases can be handled by one to many by reconsidering models.