JOINing Across Multiple Relationships

Finally this makes sense. I've been wondering about this process from soup to nuts ever since way back when Ryan gave a very brief description about this in a previous Doctrine tutorial. Thanks!

Looks like there is still some of that N+1 thing happening after adding the QuestionTag association class and updating QuestionRepository and the homepage template. FWIW, I got it down to one query by joining Answers as well in our repository's findAllAskedOrderedByNewest method, thus:

return $this->addIsAskedQueryBuilder()
     <b>->join('q.answers','a')->addSelect('a')</b>
     ->join('q.questionTags','qt')
     ->join('qt.tag','t')
     ->addSelect(['qt','t'])
     ->orderBy('q.askedAt', 'DESC')
     ->getQuery()
     ->getResult()

I'll accept my gold star now, thank you (-: ...although I guess it's a trade-off, because I have reduced the number of queries, the SELECT is selecting data that I'm not using. I bet there is a way to tell Doctrine what columns we want to fetch. Excuse me, I mean what <i>object properties</i>... So, can that be done with the QueryBuilder, or do we have to write the DQL directly?

Note that this also returns the unapproved answers - which you already noticed. :)
I fixed that by checking the answer status, but for that I had to include the Answer Entity inside the Question Entity, which does not feel right. :|

use App\Entity\Answer;

// ...
            ->leftJoin('q.answers', 'answers')
            ->addSelect('answers')
            ->andWhere('answers.status = :status')
            ->setParameter('status', Answer::STATUS_APPROVED)

Hey David,

Well done! And thanks for sharing your additional optimization on this spot with others!

Yeah, there's always a way to optimize it further, we don't pay too much attention to Doctrine optimiation in this tutorial because we're teaching different concepts here and optimization would require a separate tutorial, and probably no always needed, depends on your case and your website load :)

And yes, you're right! There's always a trade-off as you said when we're talking about optimization :) Executing less queries probably would mean that the response size will grow that leads to bigger execution time. Ideally, use some tools that helps to "profile" things, e.g. Blackfire.io tool is awesome for this - you can see if your optimization was good in your specific case. Also, we have a course about it here: https://symfonycasts.com/sc...

About fetching specific properties - yeah, it's possible, but it will reduce your DX and may complicate maintenance and increase maintenance time - as you said, there's always trade-off. And yeah, this is only possible with specific queries, i.e. when you specify explicitly what fields you want to fetch from the database. But in this case you won't be able to use "entities", because the response will be just raw array data. But that's up to you, you decide if such optimization worth in your specific case. And as I said, some profiler tools like Blackfire could very help you with this.

however, we always say in our tutorials that you should be careful with pre-optimizations, because sometimes it lead to overoptimizations :)

I hope this helps!

Cheers!