> Symfony 6 > Go Pro with Doctrine Queries
Buy Access to Course
07.

SELECT the SUM (or COUNT)

|

Share this awesome video!

|

Keep on Learning!

Start your All-Access Pass

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.