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!

  • 2018-07-24 Diego Aguiar

    Hey Abdelamine Mehdaoui

    This is odd, your query looks fine to me, can you double check that the format of your "createdAt" field is set correctly?

    > // is genus_notes is the name of table?
    Not necessarily, it's just an alias, so then you can reference to it (We like to use the name of the entity)

  • 2018-07-24 Abdelamine Mehdaoui

    Hey ryan!

    Thanks for your reply, I'm staying load other fixtures, I think that the problem in the rand function of names, because when the same names appears several times in the list of genus, date always is the same but the order does not.

    SQL code:

    "SELECT g0_.id AS id_0, g0_.name AS name_1, g0_.fun_fact AS fun_fact_2, g0_.species_count AS species_count_3, g0_.sub_family AS sub_family_4, g0_.is_published AS is_published_5 FROM genus g0_ LEFT JOIN genus_notes g1_ ON g0_.id = g1_.genus_id WHERE g0_.is_published = 1 ORDER BY g1_.created_at DESC;"""

    The result
    [0]=>
    array(6) {
    ["id_0"]=>
    string(3) "632"
    ["name_1"]=>
    string(6) "zoubir"......} // the recent date is: Sep 06, 2012
    [1]=>
    array(6) {
    ["id_0"]=>
    string(3) "636"
    ["name_1"]=>
    string(6) "zoubir" // the recent date is: Sep 06, 2012
    [2]=>
    array(6) {
    ["id_0"]=>
    string(3) "634"
    ["name_1"]=>
    string(7) "kawthar" // the recent date is: Jun 26, 2015
    array(6) {
    ["id_0"]=>
    string(3) "630"
    ["name_1"]=>
    string(6) "zoubir" // the recent date is: Sep 06, 2012

    GenusRepository:

    public function findAllPublishedOrdredByRecentlyActive()
    {
    return $this->createQueryBuilder('genus')
    ->andWhere('genus.isPublished= :isPublished')
    ->setParameter('isPublished', true)
    ->leftJoin('genus.genusnotes', 'genus_notes') // is genus_notes is the name of table?
    // genusnotes is the name of variable in GenusNote

    ->orderBy('genus_notes.createdAt', 'DESC')
    ->getQuery()
    ->execute();
    }

    // listAction function
    public function listAction()
    {
    $em = $this->getDoctrine()->getManager();
    $genuses = $em->getRepository('AppBundle:Genus')->findAllPublishedOrdredByRecentlyActive();
    return $this->render('/genus/list.html.twig', [ 'genuses' => $genuses]);
    }

  • 2018-07-23 weaverryan

    Hey Abdelamine Mehdaoui!

    Hmm, interesting! The best way to debug this is to reload the page, click the database icon in the web debug toolbar, and then go find the query inside Symfony's profiler. This will allow you to see the exact query that was made, and it might help you determine whether or not things are working correctly, or why they might not be working. Obviously, you'll be looking to make sure that the ORDER BY in the query looks correct, and that there aren't any other surprises. If everything looks ok to you, then it probably means that it IS returning the results in the correct order. In the profiler, you can even click to get a "runnable query" that you can paste directly into MySQL to see the results that Doctrine is getting.

    If the order is definitely wrong, and you can't find any issues, you can post your code and we can see if we spot any problems :).

    Cheers!

  • 2018-07-23 Abdelamine Mehdaoui

    hello, thanks for this tutorials.
    I did not have the correct order of the genuses when I try to order it by notes, but I think I put the correct code, is there something special?

  • 2018-04-30 Nicholas Clark

    That answers my question perfectly, thank you for such a quick response!

  • 2018-04-30 weaverryan

    Oh, and always remember, you can use the web debug toolbar on the page to view the actual queries that were executed, including a runnable query that you can copy and paste into your own tools, to see exactly what the results look like.

  • 2018-04-30 weaverryan

    Hey Nicholas Clark!

    Ah yes, I see your question! Indeed, I think this particular join is just a little confusing. To answer your question, no, Doctrine doesn't default to a specific note. Doctrine, fortunately, is much "dumber" than that :). What I mean is, Doctrine will simply convert this query into SQL, run that SQL, and get the result. So, the real question is, how does MySQL handle this type of a join?

    Let's look at this case specifically. Suppose there are 10 Genus and each has 10 GenusNotes (so a total of 100). If you ran this raw SQL query, it would return 100 results (because of the LEFT join): each Genus would appear 10 times, joined over to the 100 unique notes. The VERY top row would be the GenusNote with the newest createdAt (attached to whatever Genus it happens to be attached to). Then, the second row would be the second-newest GenusNote. And yea, each individual Genus would appear 10 times in the results, in whatever order its notes appear. Genus id 1 could, in theory, be the first 5 results returned, then the 20th, 50th, 90th, 95th and 99th.

    The only difference when you run this query in Doctrine is that, because we're trying to fetch Genus objects, it sees these 100 results, and simply uses the data for the 10 unique Genus rows to return 10 Genus objects (instead of returning 100 Genus objects, where each Genus is in the collection 10 times). But, thanks to the order by, the 10 Genus objects will appear in the order from the original query.

    I hope that helps! Most of the logic just comes back to how queries work, and this example is a bit odd. But, it's also important to understand that, in Doctrine, if you're asking Doctrine for Genus objects, and there are 10, it will return 10 Genus objects, even if the query has 100 rows due to a join.

    Cheers!

  • 2018-04-30 Nicholas Clark

    If a genus can have multiple notes(oneToMany), which note's createdAt are we actually ordering by?
    I can understand the usage if we were querying for notes and ordering them by a property on genus, because each note will only have a single genus.
    Does doctrine default to a specific note?
    Is there some way to specify which note you want to use?

  • 2018-04-24 weaverryan

    That's awesome! Super had you've got this working - it's kinda crazy it works so smoothly :).

  • 2018-04-23 Geoff Maddock

    I ended up finding the same suggestion you gave above with the listener and adapting it. Thus far it is working for my use cases.

    I added some configuration into the listener that defines the relations between the tables across databases. That method works nicely because I can treat them as if the are relations in the same database, at least in terms of reads.

  • 2018-04-23 weaverryan

    Hey Geoff Maddock!

    Cool question :)

    ​And actually, this has been done to join tables between MySQL and MongoDB before: https://www.doctrine-projec...

    ​The same could easily be done across 2 databases. And, I believe that this IS lazy, so that the Visitor record is only fetched when you actually access the property (not each time you query for a User).

    ​But, going a "no magic" route is also pretty cool - just centralize this logic into a service where you can pass the User to get the Visitor. Then, call that from your code. Or, when you need it in Twig, yea, just make a Twig extension that calls that service.

    Let me know what you end up trying!

    Cheers!

  • 2018-04-19 Geoff Maddock

    Here's a question that I haven't quite found the best answer for: How should I handle getting an entity relation value from an entity in a different database (stored on a different server). I have multi-database entity managers set up, and can query both databases, and return properties of events in each. However, for example I have an entity User in one db and Visitor in another db, related by User.id = Visitor.UserId. When displaying a list of Users, I'd like to get the related Visitor object and display some properties of that object, say Visitor.NumberOfVisits.

    Is there a best practice strategy for this case?

    I know I can't just set up a relation in the entity classes, it won't work if they are on separate databases. Injecting a VisitorService into the User entity seems like the wrong idea. I'm thinking maybe create a twig function that calls the service? Any suggestions or pointers?

  • 2017-11-14 Diego Aguiar

    That's awesome! I didn't even know about the "HIDDEN" statement
    I'm glad to hear that you could fix your problem :)

    Cheers!

  • 2017-11-14 Damjan Ribovski

    Hi Diego,

    Thank you for quick reply. I tweaked it a little bit and now I have drop-in replacement for GenusRepository method:

    public function findAllPublishedOrderedByRecentlyActive()
    {
    return $this->createQueryBuilder('genus')
    ->select('genus, MAX(genus_note.createdAt) as HIDDEN max_created_at')
    ->leftJoin('genus.notes', 'genus_note')
    ->where('genus.isPublished = :isPublished')
    ->setParameter('isPublished', true)
    ->groupBy('genus.id')
    ->orderBy('max_created_at', 'DESC')
    ->getQuery()
    ->execute();
    }

    The most important change was to make max_created_at to be HIDDEN.
    (Without HIDDEN doctrine didn't return array of objects, but array of arrays which contain max_created_at and a Genus object.)

    Best regards!

  • 2017-11-12 Diego Aguiar

    Hey Damjan Ribovski

    Give it a try to this code (it may need some tweaks, but I hope it gives you the idea):


    $genusRepo->createQueryBuilder('g')
    ->select('g, MAX(g.createdAt) as max_created_at')
    ->leftJoin('g.notes', 'genusNotes')
    ->where('g.isPublished = :isPublished')
    ->setParameter('isPublished', $genus->getIsPublished())
    ->groupBy('g.id')
    ->orderBy('max_created_at', 'DESC')
    ->getQuery()
    ->execute();

    Cheers!

  • 2017-11-11 Damjan Ribovski

    So that means that grouping is done on PHP side instead of MySQL side. For large "denormalized" data set that might be too inefficient. Is there a way to make Doctrine execute SQL similar to this one:

    SELECT
    g0_.id AS id_0,
    g0_.name AS name_1,
    g0_.sub_family AS sub_family_2,
    g0_.species_count AS species_count_3,
    g0_.fun_fact AS fun_fact_4,
    g0_.is_published AS is_published_5,
    MAX(g1_.created_at) as max_created_at
    FROM
    genus g0_
    LEFT JOIN genus_note g1_ ON g0_.id = g1_.genus_id
    WHERE
    g0_.is_published = 1
    group by
    g0_.id
    order by
    max_created_at desc;

    Thanks!

  • 2017-06-07 weaverryan

    Really good note! Thanks for sharing it!

  • 2017-06-04 robdig

    I came across the same issue. I fixed it by telling Alice not to make duplicate genera when creating dummy data.

    In fixtures.yml:
    Change:


    genus_{1..10}:
    name: <genus()>


    to


    genus_{1..10}:
    name(unique): <genus()>

    no more duplicate genera. In real life, I would likely set up genus.name in the database to be unique to keep our Aquanauts from inadvertently creating a duplicate genus.

  • 2017-01-25 Christophe Coevoet

    I don't think removing inversedBy is a good idea. It makes it clear that the relation is bidirectional, allowing to catch mistakes for instance (when validating the mapping, which is done for you by the profiler in DoctrineBundle).

    But this means that for most cases, you don't need a bidirectional relation anymore, which is much simpler to handle

  • 2016-09-29 Victor Bocharsky

    Hey Maksym,

    MySQL returns denormalized data as any SQL server when you use JOIN operations, that's why Doctrine normalizes this data for you. Doctrine knows that you query data FROM *genus* table, so it returns unique objects from it (actually, it hydrates objects based on denormalized data set). So all this magic handles by ORM itself.

    Cheers!

  • 2016-09-29 Maksym Minenko

    If we run this query as raw sql (something like:
    SELECT g.*, gn.createdAt FROM `genus` g
    JOIN genus_note gn
    ON gn.genus_id = g.id
    WHERE g.is_published = 1
    ORDER BY gn.createdAt DESC
    ), we get dozens of lines (it's 62 for me -- all those is_publish = 1)

    How does Doctrine know that we actually wanted to *group* them?

    I mean, without "GROUP BY g.name" line?

  • 2016-09-26 Martin

    Interesting. Thanks for the answer and for the excellent tutorial!

  • 2016-09-26 weaverryan

    Good note Martin! We're using the {genusName} in the URL for the show page, which is NOT unique in the database. That was a lazy way of doing it - in real-life, we should make a unique "slug" field. Check out this comment for some more details about that: https://knpuniversity.com/s...

    Cheers!

  • 2016-09-26 Martin

    The ranking didn't seem to be right in my /genus page, as when I clicked on the genuses, I had the following dates for the first note : Sept. 22, Sept. 19, Sept. 11, Sept. 18.

    Turns out that the array_rand() function (LoadFixtures.php) returned Cucumania three times.

    ID Published Last note
    2 no Sept. 18
    7 yes Sept. 9
    10 no August 30

    Cucumania appeared only once in my /genus list (the published one). It was ranked as if the date of the last note was September 9, but in the genus/Cucumania page, the date of the last note was September 18. That's because the showAction() method took the notes for all three ids (as it uses the genus name to select the notes).

    Just throwing that out in case in could prevent some head-scratching for someone.

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