> Symfony 3 > Mastering Doctrine Relationships in Symfony 3
Buy Access to Course
10.

Query across a JOIN (and Love it)

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

What about a JOIN query with Doctrine? Well, they're really cool.

Here's our last challenge. Go to /genus. Right now, this list is ordered by the speciesCount property. Instead, I want to order by which genus has the most recent note - a column that lives on an entirely different table.

In GenusRepository, the list page uses the query in findAllPublishedOrderedBySize(). Rename that to findAllPublishedOrderedByRecentlyActive():

24 lines | src/AppBundle/Repository/GenusRepository.php
// ... lines 1 - 7
class GenusRepository extends EntityRepository
{
// ... lines 10 - 12
public function findAllPublishedOrderedByRecentlyActive()
{
// ... lines 15 - 21
}
}

Go change it in GenusController too:

120 lines | src/AppBundle/Controller/GenusController.php
// ... lines 1 - 12
class GenusController extends Controller
{
// ... lines 15 - 42
public function listAction()
{
// ... lines 45 - 46
$genuses = $em->getRepository('AppBundle:Genus')
->findAllPublishedOrderedByRecentlyActive();
// ... lines 49 - 52
}
// ... lines 54 - 118
}

Tip

PhpStorm has a great refactoring tool to rename everything automatically. Check out the Refactoring in PhpStorm tutorial.

Adding the Join

Let's go to work! Remove the orderBy line. We need to order by the createdAt field in the genus_note table. And we know from SQL that we can't do that unless we join over to that table. Do that with, ->leftJoin('genus') - because that's the alias we set on line 15 - genus.notes:

24 lines | src/AppBundle/Repository/GenusRepository.php
// ... lines 1 - 7
class GenusRepository extends EntityRepository
{
// ... lines 10 - 12
public function findAllPublishedOrderedByRecentlyActive()
{
return $this->createQueryBuilder('genus')
->andWhere('genus.isPublished = :isPublished')
->setParameter('isPublished', true)
->leftJoin('genus.notes', 'genus_note')
// ... line 19
->getQuery()
->execute();
}
}

Why notes? This is the property name on Genus that references the relationship. And just by mentioning it, Doctrine has all the info it needs to generate the full JOIN SQL.

Joins and the Inverse Relation

Remember, this is the optional, inverse side of the relationship: we added this for the convenience of being able to say $genus->getNotes():

115 lines | src/AppBundle/Entity/Genus.php
// ... lines 1 - 11
class Genus
{
// ... lines 14 - 45
/**
* @ORM\OneToMany(targetEntity="GenusNote", mappedBy="genus")
* @ORM\OrderBy({"createdAt" = "DESC"})
*/
private $notes;
// ... lines 51 - 106
/**
* @return ArrayCollection|GenusNote[]
*/
public function getNotes()
{
return $this->notes;
}
}

And this is the second reason you might decide to map the inverse side of the relation: it's required if you're doing a JOIN in this direction.

Tip

Actually, not true! As Stof suggested in the comments on this page, it is possible to query over this join without mapping this side of the relationship, it just takes a little bit more work:

$this->createQueryBuilder('genus')
    // ...
    ->leftJoin(
        'AppBundle:GenusNote',
        'genus_note',
        \Doctrine\ORM\Query\Expr\Join::WITH,
        'genus = genus_note.genus'
    )
    // ...

Back in GenusRepository, give leftJoin() a second argument: genus_note - this is the alias we can use during the rest of the query to reference fields on the joined genus_note table. This allows us to say ->orderBy('genus_note.createdAt', 'DESC'):

24 lines | src/AppBundle/Repository/GenusRepository.php
// ... lines 1 - 7
class GenusRepository extends EntityRepository
{
// ... lines 10 - 12
public function findAllPublishedOrderedByRecentlyActive()
{
return $this->createQueryBuilder('genus')
->andWhere('genus.isPublished = :isPublished')
->setParameter('isPublished', true)
->leftJoin('genus.notes', 'genus_note')
->orderBy('genus_note.createdAt', 'DESC')
->getQuery()
->execute();
}
}

That's it! Same philosophy of SQL joining... but it takes less work.

Head back and refresh! Ok, the order did change. Look at the first one - the top note is from February 15th, the second genus has a note from February 11 and at the bottom, the most recent note is December 21st. I think we got it!

Question: when we added the join, did it change what the query returned? Before, it returned an array of Genus objects... but now, does it also return the joined GenusNote objects? No: a join does not affect what is returned from the query: we're still only selecting from the genus table. There's a lot more about that in our Doctrine Queries tutorial.

Ok, that's it! That's everything - you are truly dangerous with Doctrine now. Sure, there are some more advanced topics - like Doctrine events, inheritance and ManyToMany relations - but we'll save those for another day. Get to work on that project... or keep going with me to learn more Symfony! I promise, more bad jokes - like worse than ever.

See you next time!