Buy

Query across a JOIN (and Love it)

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!

Leave a comment!

  • 2016-09-01 weaverryan

    Hi Yang!

    No, you're absolutely correct :). If you have 2 rows with the same name, then you will have a problem. Basically:

    1) On the list page, it's no problem: all genus will be returned from the query and printed
    2) But when you go to /genus/Balaena, the findOneBy() function will find the *first* matching result.

    So, what I did in this tutorial was a bit of a "shortcut". In real-life, when I want pretty URLs like this, I'll add a "slug" field, which is *unique* in the database and is automatically set by lowercasing & cleaning out the "name" field (e.g. balaena). I use the StofDoctrineExtensionsBundle to do this, which is smart enough to make sure that this field is always unique (it would make the first one balaena and the second one balaena-1). With a unique field, you won't have this problem.

    Very good question!

  • 2016-08-30 Yang Liu

    Hi, one question. You created your genus names from a list. Is it possible query will be messed up if you got 2 genuses with the same name?
    fixtures created 2 rows with the same name "Balaena" for me. In the listAction (/genus page), datas are shown correctly(# of species is 9016 for one and 4078 for the other). however, when I dump($genus) in the showAction(/genus/Balaena page), I get the same data no matter which Balaena I clicked in the list. Did I do something wrong? Or maybe findOneBy-function has problem with data with the same name?

  • 2016-04-25 Roy Hochstenbach

    Indeed, that's what was causing it. Instead of getRepository('AppBundle:Genus') I had getRepository('AppBundle:GenusNote'). Thanks :-)

  • 2016-04-24 weaverryan

    Hi Roy!

    This is basically a "Method not found" error - the EntityRepository has a magic __call method that makes this method look different :). Three things to check:

    1) Double-check that you don't have any typos on the name in either GenusRepository or GenusController (you probably don't, as you mentioned you changed it in both spots - but just in case)

    2) Make sure that you're asking for ->getRepository('AppBundle:Genus') - make sure you're not asking for some other class's repository.

    3) Make sure that when you ask for ->getRepository('AppBundle:Genus'), you're getting back an instance of `GenusRepository`. As long as you have the property repositoryClass configuration above Genus, you should. But to be sure, you can do this:

    dump($this->getDoctrine()->getManager()->getRepository('AppBundle:Genus'));die;

    I hope one of these things helps!

  • 2016-04-21 Roy Hochstenbach

    When I change the method name to 'findAllPublishedOrderedByRecentlyActive' in both GenusRepository and GenusController, I get the following error when accessing a genus:

    Undefined method 'findAllPublishedOrderedByRecentlyActive'. The method name must start with either findBy or findOneBy!
    in vendor/doctrine/orm/lib/Doctrine/ORM/EntityRepository.php at line 226

    I've tried to clear the Symfony cache, but the issue remains.

  • 2016-03-29 weaverryan

    Wow, I did NOT know that - that's awesome (I never tried it, because even though it makes sense, it feels contrary to how most things work in Doctrine: very explicitly). I'll add a note to the tutorial. Now, if Doctrine is able to do this... I would love to remove the need to specify the inversedBy option on the mapped side. I've never (yet) looked into the code, but that has always felt like it should be unnecessary.

    Cheers!

  • 2016-03-27 Christophe Coevoet

    > 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.

    That's not true. As of Doctrine 2.3 or 2.4 (I don't remember anymore but both are already old anyway), arbitrary joins are supported, allowing to perform such joins without defining bidirectional relations.