Lucky you! You found an early release chapter - it will be fully polished and published shortly!
Rest assured, the gnomes are hard at work
completing this video!
One last challenge. On the homepage, we have seven queries. That's one to fetch the categories... and 6 more to get the fortune cookie count for each of those 6 categories.
Having 7 queries is... probably not a problem... and you shouldn't worry about optimizing performance until you actually see that there is a problem. But let's challenge ourselves to turn these seven queries into one.
Let's think: we could query for all the categories, JOIN
over to the related
fortune cookies, GROUP BY
the category, and then COUNT
the fortune cookies.
If that doesn't make sense, no worries. We'll see it in action.
Head over to FortuneController
. We're on the homepage, and we're using the
findAllOrdered()
method from $categoryRepository
. Go find that method... here
it is. We're already selecting from category
. Now also
->addSelect('COUNT(fortuneCookie.id) AS fortuneCookiesTotal')
. To join and get
that fortuneCookie
alias, add ->leftJoin('category.fortuneCookies')
, then
fortuneCookie
. Finally, for this COUNT
to work correctly, say
->addGroupBy('category.id')
.
Okay, let's see what we get! Down here, dd($query->getResult())
.
Previously, this returned an array
of Category
objects. If we
refresh... it is an array, but it's now an array of arrays where the 0
key
is the Category
object, and then we have this extra fortuneCookiesTotal
. So...
it selected exactly what we wanted! But... it changed the underlying structure.
And it kind of had to, right? It needed to somehow give us the Category
object
and the extra column behind the scenes.
Remove the dd
statement. This still returns an array
... but remove the
@return
because it no longer returns an array of Category
objects. We could
also update that to some fancier phpdoc that describes the new structure.
Next, to account for the new return, head to homepage.html.twig
. We're looping
over category in categories
... which isn't quite right now: the category is
on this 0
index. Change this to say for categoryData in categories
... then inside
add set category = categoryData[0]
. It's ugly, but more on that in a minute.
Scroll over to the length
. Instead of reaching across the relationship -
which would work, but would trigger extra queries - use
categoryData.fortuneCookiesTotal
.
Let's do this! Refresh and... just one query! Woo!
The worst part about this is that the structure of our data changed... and now
we have to read this ugly 0
key. I won't do it now, but a better solution
would be to leverage a DTO object to hold this. For example, we might create a new
class called CategoryWithFortuneCount
with two properties - $category
and
$fortuneCount
. In this repository method, we could loop over $query->getResults()
and create a CategoryWithFortuneCount
object for each one. Ultimately, our method
would return an array of CategoryWithFortuneCount
. Returning an array of objects
is much nicer than an array of arrays... with some random 0
index.
Speaking of that changed structure, if we search for something... we get an error:
Impossible to access a key "0" on an object of class
Category
.
It's... this line right here. When we search for something, we use the search()
method and... surprise! That method doesn't have the new addSelect()
and
groupBy()
: it still returns an array of Category
objects.
To fix that, create a private function
down here that can hold the group by:
addGroupByCategory(QueryBuilder $qb)
and it'll return a QueryBuilder
. Oh, and
make the argument optional... then create a new query builder if we don't
have one.
Ok, head up and steal the logic - the ->addSelect()
, ->leftJoin()
, and
->addGroupBy()
. Paste that down here. Oh, and addGroupByCategory()
isn't a
great name: use addGroupByCategoryAndCountFortunes()
.
Awesome. Above, simplify! Change this to addGroupByCategoryAndCountFortunes()
...
and then we don't need the ->addGroupBy()
, ->leftJoin()
, or ->addSelect()
.
To make sure that part is working, spin over and... head back to the homepage.
That looks good... but if we go forward... still broken. Down in search()
add $qb = $this->addGroupByCategoryAndCountFortunes($qb)
.
And now... another error:
fortuneCookie
is already defined.
Darn! But, yea, that makes sense. We're joining in our new method... and also in
addFortuneCookieJoinAndSelect()
. Fortunately, we don't need this second
call at all anymore: we were joining and selecting to solve the N+1 problem... but
now we have an even more advanced query to do that. Copy our new method, delete,
then paste it over the old one.
And now... got it! Only 1 query!
Yo friends, we did it! Woo! Thanks for joining me on this magical ride through all things Doctrine Query. This stuff is just weird, cool and fun. I hope you enjoyed it as much as I did. If you encounter any crazy situation that we haven't thought about, have any questions, or pictures of your cat, we're always here for you down in the comments. Alright, see you next time!
"Houston: no signs of life"
Start the conversation!
// composer.json
{
"require": {
"php": ">=8.1",
"ext-ctype": "*",
"ext-iconv": "*",
"beberlei/doctrineextensions": "^1.3", // v1.3.0
"doctrine/doctrine-bundle": "^2.7", // 2.9.1
"doctrine/doctrine-migrations-bundle": "^3.2", // 3.2.2
"doctrine/orm": "^2.13", // 2.15.1
"symfony/asset": "6.2.*", // v6.2.7
"symfony/console": "6.2.*", // v6.2.10
"symfony/dotenv": "6.2.*", // v6.2.8
"symfony/flex": "^2", // v2.2.5
"symfony/framework-bundle": "6.2.*", // v6.2.10
"symfony/proxy-manager-bridge": "6.2.*", // v6.2.7
"symfony/runtime": "6.2.*", // v6.2.8
"symfony/twig-bundle": "6.2.*", // v6.2.7
"symfony/webpack-encore-bundle": "^1.16", // v1.16.1
"symfony/yaml": "6.2.*" // v6.2.10
},
"require-dev": {
"doctrine/doctrine-fixtures-bundle": "^3.4", // 3.4.4
"symfony/maker-bundle": "^1.47", // v1.48.0
"symfony/stopwatch": "6.2.*", // v6.2.7
"symfony/web-profiler-bundle": "6.2.*", // v6.2.10
"zenstruck/foundry": "^1.22" // v1.32.0
}
}