Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine
TRACK

Symfony 6 >

COURSE

Go Pro with Doctrine Queries

Buy Access to Course

SELECT the SUM (or COUNT)

English Español

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $6.00
Previous Challenge

Next Chapter
Edit on GitHub

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

New goal team! Look over at the FortuneCookie entity. One of its properties is $numberPrinted, which is the number of times that we've ever printed that fortune. On the category page, up here, I want to print the total number printed for all fortunes in this category.

We could solve this by looping over $category->getFortuneCookies()... calling ->getNumberPrinted() and adding it to some $count variable. That would work as long as we always have a small number of fortune cookies. But the cookie business is booming... and soon we'll have hundreds of cookies in each category. It would be a huge slowdown if we queried for 500 fortune cookies just to calculate the sum. Actually, we'd probably run out of memory first!

Surely there's a better way, right? You bet! Do all that work in the database with a sum query.

Overriding the Selected Fields

Let's think: the data we're querying for will ultimately come from the FortuneCookie entity... so open up FortuneCookieRepository so we can add a new method there. How about: public function countNumberPrintedForCategory(Category $category): int.

73 lines src/Repository/FortuneCookieRepository.php
... lines 1 - 17
class FortuneCookieRepository extends ServiceEntityRepository
{
... lines 20 - 24
public function countNumberPrintedForCategory(Category $category): int
{
}
... lines 29 - 71
}

The query starts pretty much like they all do. Say $result = $this->createQueryBuilder('fortuneCookie'). By the way, the alias can be anything. Personally, I try to make them long enough to be unique in my project... but short enough to not be annoying. More importantly, as soon as you choose an alias for an entity, stick with it.

80 lines src/Repository/FortuneCookieRepository.php
... lines 1 - 24
public function countNumberPrintedForCategory(Category $category): int
{
$result = $this->createQueryBuilder('fortuneCookie')
... lines 28 - 34
}
... lines 36 - 80

Ok, we know that when we create a QueryBuilder, it will select all the data from FortuneCookie. But in this case, we don't want that! So, below, say ->select() to override that.

Earlier, in CategoryRepository, we used ->addSelect(), which basically says:

Take whatever we're selecting and also select this other stuff.

But this time, I'm purposely using ->select() so that it overrides that and only selects what we put next. Inside, write DQL: SUM() a function that you're probably familiar with followed by fortuneCookie. and the name of the property we want to use - numberPrinted. And you don't have to do this, but I'm going to add AS fortunesPrinted, which will name that result when it's returned. We'll see that in a minute.

80 lines src/Repository/FortuneCookieRepository.php
... lines 1 - 24
public function countNumberPrintedForCategory(Category $category): int
{
$result = $this->createQueryBuilder('fortuneCookie')
->select('SUM(fortuneCookie.numberPrinted) AS fortunesPrinted')
... lines 29 - 34
}
... lines 36 - 80

andWhere() with an Entire Entity

Ok, that takes care of the ->select(). Now we need an ->andWhere() with fortuneCookie.category = :category... calling ->setParameter() to fill in the dynamic category with the $category object.

80 lines src/Repository/FortuneCookieRepository.php
... lines 1 - 24
public function countNumberPrintedForCategory(Category $category): int
{
$result = $this->createQueryBuilder('fortuneCookie')
... line 28
->andWhere('fortuneCookie.category = :category')
->setParameter('category', $category)
... lines 31 - 34
}
... lines 36 - 80

This is interesting too! In SQL, we would normally say something like WHERE fortuneCookie.categoryId = and then the integer ID. But in Doctrine, we don't think about the tables or columns: we focus on the entities. And, there is no categoryId property on FortuneCookie. Instead, when we say fortuneCookie.category we're referencing the $category property in FortuneCookie. And instead of passing just the integer ID, we pass the entire Category object. It actually is possible to pass the ID, but most of the time you'll pass the entire object like this.

Okay, let's finish this! Convert this to a query with ->getQuery(). Below, if you think about it, we really only want one row of results. So let's say ->getOneOrNullResult(). Finally, return $result.

80 lines src/Repository/FortuneCookieRepository.php
... lines 1 - 24
public function countNumberPrintedForCategory(Category $category): int
{
$result = $this->createQueryBuilder('fortuneCookie')
->select('SUM(fortuneCookie.numberPrinted) AS fortunesPrinted')
->andWhere('fortuneCookie.category = :category')
->setParameter('category', $category)
->getQuery()
->getOneOrNullResult();
return $result;
}
... lines 36 - 80

Until now, all of our queries have returned objects. Since were selecting just one thing... does that finally change? Let's find out! Add dd($result) and then head to FortuneController to use this. For the show page controller, add an argument FortuneCookieRepository $fortuneCookieRepository. Then below, say $fortunesPrinted equals $fortuneCookieRepository->countNumberPrintedForCategory() passing $category.

81 lines src/Repository/FortuneCookieRepository.php
... lines 1 - 24
public function countNumberPrintedForCategory(Category $category): int
{
... lines 27 - 32
dd($result);
... lines 34 - 35
}
... lines 37 - 81

Beautiful! Take that $fortunesPrinted variable and pass it into Twig as fortunesPrinted.

45 lines src/Controller/FortuneController.php
... lines 1 - 6
use App\Repository\FortuneCookieRepository;
... lines 8 - 12
class FortuneController extends AbstractController
... lines 14 - 30
public function showCategory(int $id, CategoryRepository $categoryRepository, FortuneCookieRepository $fortuneCookieRepository): Response
{
... lines 33 - 36
$fortunesPrinted = $fortuneCookieRepository->countNumberPrintedForCategory($category);
... line 38
return $this->render('fortune/showCategory.html.twig',[
... line 40
'fortunesPrinted' => $fortunesPrinted,
]);
}
}

Finally, find the template - showCategory.html.twig - and... there's a table header that says "Print History". Add some parentheses with {{ fortunesPrinted }}. Add |number_format to make this prettier then the word total.

40 lines templates/fortune/showCategory.html.twig
... lines 1 - 8
<table class="table-auto border mb-6">
<thead class="bg-slate-500 text-white">
... lines 11 - 14
<th class="border p-4">
Print History ({{ fortunesPrinted|number_format }} total)
</th>
... line 18
</thead>
... lines 20 - 31
</table>
... lines 33 - 40

Awesome! Since we have that dd(), let's refresh and... look at that! We get an array back with 1 key called fortunesPrinted! Yup, as soon as we start selecting specific data, we just get back that specific data. It's exactly like you'd expect with a normal SQL query.

If we had said ->select('fortuneCookie') (which is redundant because that's what createQueryBuilder() already does), that would have given us a FortuneCookie object. But as soon as we're selecting one specific thing, it gets rid of the object and returns an associative array.

Using getSingleScalarResult()

Because our method should return an int, we could complete this by saying return $result['fortunesPrinted']. But if you have a situation where you're selecting one row of data... and only one column of data, there's a shortcut to get that one column: ->getSingleScalarResult(). We can return that directly.

81 lines src/Repository/FortuneCookieRepository.php
... lines 1 - 17
class FortuneCookieRepository extends ServiceEntityRepository
{
... lines 20 - 24
public function countNumberPrintedForCategory(Category $category): int
{
$result = $this->createQueryBuilder('fortuneCookie')
... lines 28 - 31
->getSingleScalarResult();
... lines 33 - 35
}
... lines 37 - 79
}

I'll keep the dd() so we can see it. And... awesome! We get just the number! Well, technically it's a string. If you want to be strict, you can add (int). And now... got it! We have a nicely formatted total number!

81 lines src/Repository/FortuneCookieRepository.php
... lines 1 - 24
public function countNumberPrintedForCategory(Category $category): int
{
... lines 27 - 34
return (int) $result;
}
... lines 37 - 81

Next: Let's select even more data and see how that complicates things.

Leave a comment!

Most Useful Newest Oldest
0
Login or Register to join the conversation
Cat in space

"Houston: no signs of life"
Start the conversation!

Symfony 6

What PHP libraries does this tutorial use?

// 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
    }
}
Edit on GitHub
Previous Challenge
Next Chapter
userVoice