> Symfony 5 > Mastering Doctrine Relations
Login to bookmark this video
Buy Access to Course
20.

Joining Across a ManyToMany

Share this awesome video!

|

Keep on Learning!

Start your All-Access Pass

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

Each published question now relates to 0 to 5 random tags. Over on the homepage, let's render the list of tags for each question under its vote count.

Rendering the ManyToMany Relation

And I'm happy to report that using a ManyToMany relationship... isn't anything special. Open up the template for this page: templates/question/homepage.html.twig. Down here... right after the vote string, add {% for tag in question.tags %}.

53 lines | templates/question/homepage.html.twig
// ... lines 1 - 9
<div class="container">
// ... lines 11 - 15
<div class="row">
{% for question in questions %}
<div class="col-12 mb-3">
<div style="box-shadow: 2px 3px 9px 4px rgba(0,0,0,0.04);">
<div class="q-container p-4">
<div class="row">
<div class="col-2 text-center">
// ... lines 23 - 26
{% for tag in question.tags %}
// ... line 28
{% endfor %}
</div>
// ... lines 31 - 38
</div>
</div>
// ... lines 41 - 45
</div>
</div>
{% endfor %}
</div>
</div>
// ... lines 51 - 53

It's that easy: our Question object has a tags property that will return a collection of all the related Tag objects. Behind the scenes, to get this data, Doctrine will need to query across the join table and the tag table. But... we don't really care about that! We just get to say question.tags and that returns all the Tag objects for this Question. It's really no different than how we could say question.answers to get all of the answers for a question.

So inside the loop, we're dealing with a Tag object. Add a span, print {{ tag.name }}... and then I'll give this a couple of classes to make it look cool.

53 lines | templates/question/homepage.html.twig
// ... lines 1 - 9
<div class="container">
// ... lines 11 - 15
<div class="row">
{% for question in questions %}
<div class="col-12 mb-3">
<div style="box-shadow: 2px 3px 9px 4px rgba(0,0,0,0.04);">
<div class="q-container p-4">
<div class="row">
<div class="col-2 text-center">
// ... lines 23 - 26
{% for tag in question.tags %}
<span class="badge rounded-pill bg-light text-dark">{{ tag.name }}</span>
{% endfor %}
</div>
// ... lines 31 - 38
</div>
</div>
// ... lines 41 - 45
</div>
</div>
{% endfor %}
</div>
</div>
// ... lines 51 - 53

Let's try this thing! Refresh and... done! We're awesome.

Joining in a Query with a ManyToMany

But check out the queries on this page: there are 41! Yikes! If you open this up, we have another N+1 problem. This first query is from the question table: it returns all of the questions. This second query selects the tag data for a specific question... this is triggered when the question.tags line is executed. Then... if you keep looking down - skip this one - we have that same query for the next question... and the same query for the next... and the next. We also have extra queries for counting the answers for each question, but ignore those right now.

So... when we render the tags for each question, we have the N+1 query problem! When we had this problem before on the answers page, we fixed it inside of AnswerRepository... by joining across the question relationship and then selecting the question data. We can do the exact same thing again.

The controller for this page is src/Controller/QuestionController.php... it's the homepage() method.

82 lines | src/Controller/QuestionController.php
// ... lines 1 - 15
class QuestionController extends AbstractController
{
// ... lines 18 - 30
public function homepage(QuestionRepository $repository)
{
$questions = $repository->findAllAskedOrderedByNewest();
return $this->render('question/homepage.html.twig', [
'questions' => $questions,
]);
}
// ... lines 39 - 80
}

To fetch the questions, we're already calling a custom repository method called findAllAskedOrderedByNewest().

Let's go find that: open up QuestionRepository. Here it is. So far, it's pretty simple: it makes sure that the askedAt is not null - that's this addIsAskedQueryBuilder() part - and then orders the newest first.

58 lines | src/Repository/QuestionRepository.php
// ... lines 1 - 15
class QuestionRepository extends ServiceEntityRepository
{
// ... lines 18 - 25
public function findAllAskedOrderedByNewest()
{
return $this->addIsAskedQueryBuilder()
->orderBy('q.askedAt', 'DESC')
->getQuery()
->getResult()
;
}
private function addIsAskedQueryBuilder(QueryBuilder $qb = null): QueryBuilder
{
return $this->getOrCreateQueryBuilder($qb)
->andWhere('q.askedAt IS NOT NULL');
}
// ... lines 40 - 56
}

To fix the N+1 problem, we need to add a join. And this is where things get interesting. In the database, we need to join from question to question_tag... and then join from question_tag over to tag. So we actually need two joins.

But in Doctrine, we get to pretend like that join table doesn't exist: Doctrine wants us to pretend that there is a direct relationship from question to tag. What I mean is, to do the join, all we need is ->leftJoin() - because we want to get the many tags for this question - q.tags, tag.

60 lines | src/Repository/QuestionRepository.php
// ... lines 1 - 15
class QuestionRepository extends ServiceEntityRepository
{
// ... lines 18 - 25
public function findAllAskedOrderedByNewest()
{
return $this->addIsAskedQueryBuilder()
->orderBy('q.askedAt', 'DESC')
->leftJoin('q.tags', 'tag')
// ... lines 31 - 33
;
}
// ... lines 36 - 58
}

That's it. We reference the tags property on question... and let Doctrine figure out how to join over to that. The second argument - tag - becomes the alias to the data on the tag table. We need that to select its data: addSelect('tag').

60 lines | src/Repository/QuestionRepository.php
// ... lines 1 - 15
class QuestionRepository extends ServiceEntityRepository
{
// ... lines 18 - 25
public function findAllAskedOrderedByNewest()
{
return $this->addIsAskedQueryBuilder()
->orderBy('q.askedAt', 'DESC')
->leftJoin('q.tags', 'tag')
->addSelect('tag')
// ... lines 32 - 33
;
}
// ... lines 36 - 58
}

So... yup! Joining across a ManyToMany relationship is no different than joining across a ManyToOne relationship: you reference the relation property and Doctrine does the heavy lifting.

Try it now. We have 41 queries and... when we refresh... yes! Down to 21! Open up the profiler and look at that first query... it's pretty awesome. It selects all of the question data... and then took care of left joining over to question_tag, left joining again over to tag and then selecting the tag data. So cool!

Next: the question_tag table - the join table - only has 2 columns: question_id and tag_id. What if we wanted to add more columns to this? Like a taggedAt date column? There's no entity class for this table... so is adding a 3rd or 4th column even possible? The answer is yes: but it does require some changes.