How does inner join work on a many-to-many relationship using Doctrine and Symfony2
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>";
}
}
Related videos on Youtube
pinch boi triggered af
Hey B0$$ >:} i code and stuff, in a dark damp rat infested dungeon. but the vibes are good though
Updated on June 15, 2022Comments
-
pinch boi triggered af almost 2 years
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 simpleManyToMany
relationship betweengroups
andteam
, there will be agroups_team
tables that will automatically be created heregroups 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. -
pinch boi triggered af over 8 yearshi thanks for the answer, yes i did consider
OneToMany
-ManyToOne
in place ofManyToMany
. but, a big BUT, its defenitly not the same, you have a 'one' side to theOneToMany - ManyToOne
which means we have to use a loop to insert the records, and this is not only messy, but also involves a lot of custom code (hint: it involves usingclone
ordetach()
shivers!) to do a simple CRUD operation. and hence the good olManyToMany
.