Buy

ManyToMany Joins & When to Avoid ManyToMany

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

Login Subscribe

We have the N+1 query problem once again. Click to view those queries. The new queries are mixed in here, but you'll see 7 new queries that select from tag with an INNER JOIN so that it can find all the tags for just one Article. Each time we reference the tags for a new Article, it makes a new query for that article's tags.

This is quite possibly not something you need to worry about, at least, not until you can see a real performance issue on production. But, we should be able to fix it. The first query on this page finds all of the published articles. Could we add a join to that query to select the tag data all at once?

Totally! Open ArticleController and find the homepage() action. Right now, we're using $articles = $repository->findAllPublishedOrderedByNewest():

65 lines src/Controller/ArticleController.php
... lines 1 - 13
class ArticleController extends AbstractController
{
... lines 16 - 28
public function homepage(ArticleRepository $repository)
{
$articles = $repository->findAllPublishedOrderedByNewest();
... lines 32 - 35
}
... lines 37 - 63
}

Open ArticleRepository to check that out:

58 lines src/Repository/ArticleRepository.php
... lines 1 - 15
class ArticleRepository extends ServiceEntityRepository
{
... lines 18 - 22
/**
* @return Article[]
*/
public function findAllPublishedOrderedByNewest()
{
return $this->addIsPublishedQueryBuilder()
->orderBy('a.publishedAt', 'DESC')
->getQuery()
->getResult()
;
}
... lines 34 - 56
}

This custom query finds the Article objects, but does not do any special joins. Let's add one. But.... wait. This is weird. If you think about the database, we're going to need to join twice. We first need a LEFT JOIN from article to article_tag. Then, we need a another JOIN from article_tag to tag so that we can select the tag's data.

This is where Doctrine's ManyToMany relationship really shines. Don't think at all about the join table. Instead, ->leftJoin() on a.tags and use t as the new alias:

60 lines src/Repository/ArticleRepository.php
... lines 1 - 15
class ArticleRepository extends ServiceEntityRepository
{
... lines 18 - 22
/**
* @return Article[]
*/
public function findAllPublishedOrderedByNewest()
{
return $this->addIsPublishedQueryBuilder()
->leftJoin('a.tags', 't')
... lines 30 - 33
;
}
... lines 36 - 58
}

The a.tags refers to the tags property on Article. And because Doctrine knows that this is a ManyToMany relationship, it knows how to join all the way over to tag. To actually fetch the tag data, use ->addSelect('t'):

60 lines src/Repository/ArticleRepository.php
... lines 1 - 15
class ArticleRepository extends ServiceEntityRepository
{
... lines 18 - 22
/**
* @return Article[]
*/
public function findAllPublishedOrderedByNewest()
{
return $this->addIsPublishedQueryBuilder()
->leftJoin('a.tags', 't')
->addSelect('t')
... lines 31 - 33
;
}
... lines 36 - 58
}

That is it. Go back to our browser. The 15 queries are... back down to 8! Open the profiler to check them out. Awesome! The query selects everything from article and all the fields from tag. It can do that because it has both joins! That's nuts!

When a ManyToMany Relationship is Not What You Need

Ok guys, there is one last thing we need to talk about, and, it's a warning about ManyToMany relations.

What if we wanted to start saving the date of when an Article was given a Tag. Well, crap! We can't do that. We could record the date that a Tag was created or the date an Article was created, but we can't record the date when an Article was linked to a Tag. In fact, we can't save any extra data about this relationship.

Why? Because that data would need to live on this article_tag table. For example, we might want a third column called created_at. The problem is, when you use a ManyToMany relationship, you cannot add any more columns to the join table. It's just not possible.

This means that if, in the future, you do need to save extra data about the relationship, well, you're in trouble.

So, here's my advice: before you set up a ManyToMany relationship, you need to think hard and ask yourself a question:

Will I ever need to store additional metadata about this relationship?

If the answer is yes, if there's even one extra piece of data that you want to store, then you should not use a ManyToMany relationship. In fact, you can't use Doctrine at all, and you need to buy a new computer.

I'm kidding. If you need to store extra data on the article_tag table, then, instead, create a new ArticleTag entity for that table! That ArticleTag entity would have a ManyToOne relationship to Article and a ManyToOne relationship to Tag. This would effectively give you the exact same structure in the database. But now, thanks to the new ArticleTag entity, you're free to add whatever other fields you want.

If you generated a ManyToMany relationship by mistake and want to switch, it's not the end of the world. You can still create the new entity class and generate a migration so that you don't lose your existing data. But, if you can configure things in the beginning... well, even better.

Ok guys, you are now Doctrine pros! Your relationship skills strike fear at the heart of your enemies, and your ability to JOIN across tables is legendary among your co-workers.

Yes, there is more to learn, like how to write even more complex queries, and there are a lot of other, cool features - like Doctrine inheritance. But, all of the super important stuff that you need to create a real site? Yea, you got it down. So go out there, SELECT * FROM world, and build something amazing with Doctrine.

Alright guys, seeya next time.

Leave a comment!

  • 2018-07-03 Victor Bocharsky

    Hey Greg,

    Thanks for sharing it! IIRC, by default DataTables load ALL the data and then paginate over the loaded data with JavaScript, i.e. it's not AJAX-based by default. But it can be configured to load data from the server by AJAX requests, so yeah, it may work for Yahya as well.

    Cheers!

  • 2018-07-03 Greg

    https://datatables.net/ is super-cool for JS pagination.

  • 2018-06-27 Diego Aguiar

    Ah man, thanks!

  • 2018-06-27 Mouad Errahmouni

    Victor Bocharsky Am waiting for the translations course, thank you.

  • 2018-06-27 Mouad Errahmouni

    Great Symfony Tutorials i ever found in the web, Thank you, you are the best.

  • 2018-06-26 Victor Bocharsky

    Hey Yahya,

    Thanks! :) Well, we're going to make an entire course about translations, it should be released soon. For other topics, some extensions from StofDoctrineExtensionsBundle we mention in our Symfony tutorials, use our updated search to find ones. About other ideas - would be cool to have screencasts about them too, and we have them in mind for the future, but I have no estimations when it may be released yet.

    > Another topic but is there AJAX based alternative to knp-paginator-bundle?

    I personally do not know any specific bundle, but we do talk about pagination in this course: https://knpuniversity.com/s... . You may also check this screencast: https://knpuniversity.com/s... . That's an API way to do pagination. But you can check some popular JS libs on GitHub that helps with infinite scroll, etc. Along with KnpPaginatorBundle or PagerfantaBundle you can implement it easy.

    Cheers!

  • 2018-06-24 Yahya A. Erturan

    Great tutorials, thank you.

    Can you cover more topics, especially the ones most web developers encounter, navigation sortable trees, saving entities with translatable fields, versioning and logging best practices, and of course uploading media. I saw most of them in Stof Doctrine Extensions. However, you guys making everything easier then it really is :)

    Another topic but is there AJAX based alternative to knp-paginator-bundle?

    Thanks for all your help.

  • 2018-06-22 Victor Bocharsky

    Hey Etienne,

    Thanks for sharing this, good post.

    Cheers!

  • 2018-06-22 Etienne Lp

    Hey again !

    Just to share with others, I read this article from @Iltar https://stovepipe.systems/p..., that proposes the DTO pattern in order to avoiding entities in forms.

    Cheers !

  • 2018-06-21 Etienne Lp

    Hey weaverryan !

    Cool, I'm reassured now ! I think i will use most of the time this way instead of the Form Component (Unless Form could bring me cool stuff without to many complication for shure ;) )

    Thanks again,
    Love your tutorials !

  • 2018-06-20 weaverryan

    Hey Etienne Lp!

    Good job on the plural stuff :).

    > When you build a javascript front-end instead of form component, is there a trick when you wanna map the data to your entity in order to take advantage of the Validator component ? Or mb you simply hydrate your object by hand and then send it to the validator ?

    Yep, I would hydrate it by hand and send it to the validator. It's pretty easy. The only trick is that the validator returns errors in a "funny" format, so I usually need to loop over the errors and create an array that makes more sense to me. As an example, check out this code block - https://knpuniversity.com/s... - expand the entire file, and look for getErrorsFromForm(). This uses the form, so using the validator will be a bit different. But, the idea will be the same: the Valdiator will return an "iterable" object (i.e. something you can loop over). You can loop over this to create a simple array.

    Btw, for the hydration part, you could use the form component or the serializer if your reading it a lot of data. But if you're doing something simple, just keep it simple and hydrate by hand.

    Cheers!

  • 2018-06-20 Etienne Lp

    Hello weaverryan !

    Again... Thanks for your help. All works perfectly :)
    I also fixed my singular / plural, was confusing me.

    In your first comment you said :
    > "Btw, at some point, if your form became even more and more complex, it would be better to build a JavaScript front-end instead of using the form component"

    When you build a javascript front-end instead of form component, is there a trick when you wanna map the data to your entity in order to take advantage of the Validator component ? Or mb you simply hydrate your object by hand and then send it to the validator ?

    Cheers !

  • 2018-06-19 weaverryan

    Hey Etienne Lp!

    Bah, this was my fault! The code I gave you was wrong. It should be:


    $builder->add('userIndicators', CollectionType::class, [
    'entry_type' => UserIndicatorsFormType::class
    ]);

    As you mentioned, userIndicators is an array (well, a collection object, but, for the form, it's the same thing: it is *many* UserIndicators objects, not just one). So, you need to use the CollectionType to render many of these. I totally meant to put this in my original code - sorry!

    > Note : Maybe not very important , but someone tell me that the composite primary keys are no longuer supported in Doctrine, should I instead add an ID autoincrement in `UsersIndicators` Entity ?

    I'm not sure if this is true or not, but I always add a normal primary key (i.e. do not use composite primary keys) because it just makes things simpler. I'm sure there's some minor cool things about having the composite primary keys... but I like making the entity act like all my other entities.

    Cheers!

  • 2018-06-19 Etienne Lp

    Hey weaverryan,

    So much tanks for your answer !

    I changed my code in order to have the same as you expeted.
    I think i'm close to win this buuut, i got already the same error exception. I Know why it crashed but i dunno how to fix it.
    In my controller, the `$user` variable is an User Object. In this User Object (Entity), i got the property $userIndicators that map to the `UsersIndicators` Entity. BUT, this is an arrayCollection, and when i passed the $user Object to the Form, Symfony crash with :

    > "The form's view data is expected to be an instance of class App\Entity\UsersIndicators, but is an instance of class Doctrine\ORM\PersistentCollection. You can avoid this error by setting the "data_class" option to null or by adding a view transformer that transforms an instance of class Doctrine\ORM\PersistentCollection to an instance of App\Entity\UsersIndicators."

    Maybe i totally misunderstood something ?

    Note : Maybe not very important , but someone tell me that the composite primary keys are no longuer supported in Doctrine, should I instead add an ID autoincrement in `UsersIndicators` Entity ?

    Thanks again !
    ______

    Edit : Just in order to illustrate where I wanna go :

    Indicator 1 : checkbox (for displayed (boolean)) --- color picker (for color (string))
    Indicator 2 : checkbox (for displayed (boolean)) --- color picker (for color (string))
    Indicator 3 : checkbox (for displayed (boolean)) --- color picker (for color (string))

  • 2018-06-18 weaverryan

    Hey Etienne Lp!

    First, nice job setting up the OneToMany / ManyToOne relationships so that you can store your extra fields on the "middle" entity! Second, yep, *forms* are where things typically get really complex. In your case, fortunately, your form and your entities are actually pretty "close". I mean, you are trying to render a form with the same hierarchy and fields as your entities. And so, we should be able to build a form that does this pretty easily. Here is my advice:

    1) Create a form for your UserIndicators entity - e.g. UserIndicatorsFormType (I think you already did this, but just listing it for clarity). Give this two fields: color & displayed. And, set the data_class option to UserIndicators.

    2) Create a form for your User class first. I'm going to assume that the entire purpose of this form is to edit the "indicators", but not edit any other user information. In that case, you could have a EditIndicatorsUserFormType class. Inside, give it just one field (you can put other fields from your User if you want to): userIndicators (or whatever the property name is on User for the relationship). This field should look like this:

    $builder->add('userIndicators', UserIndicatorsFormType::class);

    3) Finally, in your controller, you will actually build the form around your User entity:


    $form = $this->createForm(EditIndicatorsUserFormType::class, $user);

    So, you're *actually* creating a form for your User, inside, we're modifying the userIndicators property.

    Let me know if this helps! Btw, at some point, if your form became even more and more complex, it would be better to build a JavaScript front-end instead of using the form component. But, from what you've told me, the form system should work great here.

    Cheers!

  • 2018-06-18 Etienne Lp

    Hey , thanks for those tutorials !

    But got a question for you guys :

    My code :
    I got a relation OneToMany / ManyToOne : Entity User ==> Entity UsersIndicators <== Entity Indicator.
    (Note : I got a composite primary key in UsersIndicators on the properties "$user" and "$indicator" )
    The Entity "UsersIndicators" got extra fields like "$color" and "$displayed".

    My problem :
    I have trouble creating my form.
    I need to create a Form that, for a connected User, a list of his Indicators are displayed and for each indicator, the user could select the color (string), and if if want to display it (boolean). Not to add or delete indicators.

    I try to create a formType for the UsersIndicators Entity, but in my Controller, when i createForm with the FormType, i passed at the second argument the $userIndicator propertie of the User Entity. This is actually an ArrayCollection of UsersIndicators !

    Symfony tells me, in the exception, to add a viewTransformer but i'm a little lost...
    Any idea ?

    Thanks in advance

  • 2018-05-15 Victor Bocharsky

    Hey Matt,

    Not sure about adding more chapters to this course, this one is *almost* the final list, but we may tweak or add something during releasing videos if we'll have some ideas, so it's difficult to say 100% :)

    Yeah, definitely we're going to release new Symfony 4 related courses, we do not talk about Symfony Forms yet, etc. But we're also going to release ReactJS course soon which will be built on Symfony 4 of course.

    Cheers!

  • 2018-05-14 Matt Johnson

    Thanks, Victor. You guys are really cranking through them!

    So, will there be more steps added to this course or will the next update to the Symfony4 series be a course after this one?

  • 2018-05-14 Victor Bocharsky

    Hey Matt,

    We're working on this and further chapters right now, as soon as they are ready - we'll release them! And yeah, good question! Actually, I'm working on refactoring these notifications system right now, when this feature is deployed (should be soon) you will have 2 notifications for subscribed courses: one when the 1st chapter is published and another one when the the course is completely released (i.e. all chapters are released).

    We're trying to release new videos each day, so probably email users every day about new videos would be too often and smell spammy :) Thanks for understanding!

    But if you want to get notifications about each chapter, we actually have some kind of them already, you can check our "Course Updates" page: https://knpuniversity.com/p... .

    Cheers!

  • 2018-05-12 Matt Johnson

    Guess that's the end of the road for now! I saw the "notified when course is completed" option, but will there be a way to get notifications when more of these incomplete sections are added?

    Thanks!