> Symfony 7 > Symfony, Doctrine Relations & Warp Drive Basics
Login to bookmark this video
Buy Access to Course
19.

Joining Across a Many-to-Many Relationship

|

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

Ever wondered which starship in the fleet is crawling with the most droids? Me too! Let's list every ship in ascending order based on their droid count.

Dive into src/Controller/MainController.php. The query is: $ships = $repository->findIncomplete();.

Click into that method and give it a new, snazzy name: findIncompleteOrderedByDroidCount():

67 lines | src/Repository/StarshipRepository.php
// ... lines 1 - 14
class StarshipRepository extends ServiceEntityRepository
{
// ... lines 17 - 24
public function findIncompleteOrderedByDroidCount(): Pagerfanta
{
// ... lines 27 - 34
}
// ... lines 36 - 65
}

Copy that, swing back to the controller, and replace the old method with the new one:

30 lines | src/Controller/MainController.php
// ... lines 1 - 10
class MainController extends AbstractController
{
#[Route('/', name: 'app_homepage')]
public function homepage(
// ... lines 15 - 16
): Response {
$ships = $repository->findIncompleteOrderedByDroidCount();
// ... lines 19 - 27
}
}

We've changed nothing yet, so a quick refresh gives us the same stuff.

To order the starships by their droid count, we need to join across the join table all the way to droid, group by starship, and then count the droids. Woof. Actually, it's quite nice!

In StarshipRepository, add a leftJoin(). But we're not going to think about the join table or the database. Nope, focus only on the relationships in Doctrine. So we're joining across s, which is our starship, and droids, the property that has the ManyToMany relationship to Droid. Finally, we alias those droids as droid.

To count the droids, add a groupBy('s.id').

To order replace the existing orderBy() with orderBy('COUNT(droid)', 'ASC'):

69 lines | src/Repository/StarshipRepository.php
// ... lines 1 - 14
class StarshipRepository extends ServiceEntityRepository
{
// ... lines 17 - 24
public function findIncompleteOrderedByDroidCount(): Pagerfanta
{
$query = $this->createQueryBuilder('s')
// ... line 28
->orderBy('COUNT(droid)', 'ASC')
->leftJoin('s.droids', 'droid')
->groupBy('s.id')
// ... lines 32 - 33
;
// ... lines 35 - 36
}
// ... lines 38 - 67
}

After that, hit refresh and boom! At the top, you'll see droids none. But as you scroll down, the droid count increases. If you're brave enough to venture a few pages ahead, we start to see starships with two, three, or even four droids!

The key? There's nothing special about this join. We join across the property and Doctrine handles the rest.

If you peek at the query on this page, you'll see it's handling all the details. Search for starship_droid to find the query. This is ugly, but if you format the query, it selects from starship, taking care of the join over to the join table and joining again over to droid. That allows us to count and order by that count on that droid table. Impressive Doctrine, impressive!

That's technically it for ManyToMany! But next we're going to handle a more advanced, but still common, use case: adding data to the join table, like the date when the droid joined the starship.