Query across a JOIN (and Love it)

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

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!

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

Thank you guys, so tricky, does there any difference between ?

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

and

 
    ->innerJoin(
        'AppBundle:GenusNote',
        'genus_note',
        \Doctrine\ORM\Query\Expr\Join::WITH,
        'genus = genus_note.genus'
    ) 

Hey Ahmedbhs,

LEFT JOIN and INNER JOIN are different - LEFT JOIN takes all data from the 1st table and add data from the 2nd table using NULL if no corresponding data from the 2nd table. INNER JOIN takes only data that exist in both tables. Not sure if I explained well for you, probably better to read about INNER JOIN and LEFT JOIN e.g. on wiki to see the difference on real examples.

Hope this helps!

Cheers!

Yeah I see the difference, but in our case we are adding a condition on the left join : WITH 'genus = genus_note.genus' so I think we gonna get the same result whether we use leftJoin with condition on id or an innerJoin.

Hey Ahmedbhs,

Ah, now I see what you mean... Hm, it still should be difference as you use different join types, but I can't say too much, I think I never use those condition types before as they are optional. Have you tested it?

Cheers!

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.

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!

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

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?

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!

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.

Really good note! Thanks for sharing it!

Hi.

Can anyone please help.
I'm struggling to write this query inside symfony:

select Count(offers.product_id) as offer_count, products.name FROM products JOIN offers on offers.product_id=products.product_id group by offers.product_id

Thanks!

Hey Vesna!

Sorry for the slow response. If you really only need to return the count and the product name, I'd just write this in raw SQL - no great reason to try to put it into DQL/query builder: https://symfonycasts.com/sc...

Cheers!

Hey there

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)

Hey there!

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!

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.

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!

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

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?

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!

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.

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

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?

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!

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.

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

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?

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!

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!

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!

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!

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

Cheers!