This tutorial has a new version, check it out!
> Symfony 5 > Go Pro with Doctrine Queries (Legacy)
Login to bookmark this video
Buy Access to Course
06.

SELECT the SUM (or COUNT)

Share this awesome video!

|

Keep on Learning!

Start your All-Access Pass

In every query so far, Doctrine gives us objects. That's its default mode, but we can also easily use it to select specific fields.

On our category page, you can see how many of each fortune has been printed over time. At the top, let's total those numbers with a SUM() query and print it out.

In showCategoryAction(), create a new variable - $fortunesPrinted, that'll be a number. And of course, we'll write a new query to get this. But instead of shoving this into CategoryRepository, this queries the FortuneCookie entity, so we'll use its repository instead. So, AppBundle:FortuneCookie, and we'll call a new countNumberPrintedForCategory method. Pass the $category object as an argument:

59 lines | src/AppBundle/Controller/FortuneController.php
// ... lines 1 - 9
class FortuneController extends Controller
{
// ... lines 12 - 35
public function showCategoryAction($id)
{
// ... lines 38 - 47
$fortunesPrinted = $this->getDoctrine()
->getRepository('AppBundle:FortuneCookie')
->countNumberPrintedForCategory($category);
// ... lines 51 - 56
}
}

This will return the raw, summed number. To actually use this, pass this into the template:

59 lines | src/AppBundle/Controller/FortuneController.php
// ... lines 1 - 35
public function showCategoryAction($id)
{
// ... lines 38 - 47
$fortunesPrinted = $this->getDoctrine()
->getRepository('AppBundle:FortuneCookie')
->countNumberPrintedForCategory($category);
// ... lines 51 - 52
return $this->render('fortune/showCategory.html.twig',[
'category' => $category,
'fortunesPrinted' => $fortunesPrinted,
]);
}
// ... lines 58 - 59

And now print it out: {{ fortunesPrinted }}, put that through Twig's number_format filter and add the word "total":

36 lines | app/Resources/views/fortune/showCategory.html.twig
// ... lines 1 - 12
<th>
Printed History ({{ fortunesPrinted|number_format }} total)
</th>
// ... lines 16 - 36

Amazing. You already know the next step: we need to create a new countNumberPrintedForCategory method inside of FortuneCookieRepository and make it query not for an object, but just a single number: the sum of how many times each fortune has been printed. That means we'll be totaling the numberPrinted property on FortuneCookie:

176 lines | src/AppBundle/Entity/FortuneCookie.php
// ... lines 1 - 12
class FortuneCookie
{
// ... lines 15 - 45
/**
* @var integer
*
* @ORM\Column(name="numberPrinted", type="integer")
*/
private $numberPrinted;
// ... lines 52 - 174
}

Open FortuneCookieRepository and add the new public function. We're expecting a Category object, so I'll type-hint the argument like a nice, respectable programmer. Every query starts the same: $this->createQueryBuilder() and we'll use fc as the alias. Keep the alias consistent for an entity, it'll save you heartache later.

Next, we need an andWhere() because we need to only find FortuneCookie results for this Category. So, fc.category - because category is the name of the property on FortuneCookie for the relationship. Now, equals :category. And next, we'll set that parameter:

26 lines | src/AppBundle/Entity/FortuneCookieRepository.php
// ... lines 1 - 14
public function countNumberPrintedForCategory(Category $category)
{
return $this->createQueryBuilder('fc')
->andWhere('fc.category = :category')
->setParameter('category', $category)
// ... lines 20 - 22
}
// ... lines 24 - 26

This looks like every query we've made before, and if we finish now, it'll return FortuneCookie objects. That's lame - I want just the sum number. To do this, call select(). Nope, this is not addSelect like we used before. When we call createQueryBuilder from inside FortuneCookieRepository, the query builder has a ->select('fc') built into it. In other words, it's selecting everything from FortuneCookie. Calling select() clears out anything that's being selected and replaces it with our SUM(fc.numberPrinted) as fortunesPrinted:

26 lines | src/AppBundle/Entity/FortuneCookieRepository.php
// ... lines 1 - 14
public function countNumberPrintedForCategory(Category $category)
{
return $this->createQueryBuilder('fc')
->andWhere('fc.category = :category')
->setParameter('category', $category)
->select('SUM(fc.numberPrinted) as fortunesPrinted')
// ... lines 21 - 22
}
// ... lines 24 - 26

We're giving the value an alias, just like you can do in SQL. Now, instead of an object, we're getting back a single field. Let's finish it! Add getQuery().

Last, should we call execute() or getOneOrNullResult()? If you think about the query in SQL, this will return a single row that has the fortunesPrinted value. So we want to return just one result - use getOneOrNullResult():

26 lines | src/AppBundle/Entity/FortuneCookieRepository.php
// ... lines 1 - 14
public function countNumberPrintedForCategory(Category $category)
{
return $this->createQueryBuilder('fc')
->andWhere('fc.category = :category')
->setParameter('category', $category)
->select('SUM(fc.numberPrinted) as fortunesPrinted')
->getQuery()
->getOneOrNullResult();
}
// ... lines 24 - 26

Love it! I'm curious to see what this query returns, so let's var_dump $fortunesPrinted inside our controller:

59 lines | src/AppBundle/Controller/FortuneController.php
// ... lines 1 - 35
public function showCategoryAction($id)
{
// ... lines 38 - 47
$fortunesPrinted = $this->getDoctrine()
->getRepository('AppBundle:FortuneCookie')
->countNumberPrintedForCategory($category);
var_dump($fortunesPrinted);die;
// ... lines 52 - 56
}
// ... lines 58 - 59

Refresh! It's just what you'd expect: an array with a single key called fortunesPrinted. So the $fortunesPrinted variable isn't quite a number - it's this array with a key on it. But let me show you a trick. I told you about execute() and getOneOrNullResult(): the first returns many results, the second returns a single result or null. But if you're returning a single row that has only a single column, instead of getOneOrNullResult(), you can say getSingleScalarResult():

26 lines | src/AppBundle/Entity/FortuneCookieRepository.php
// ... lines 1 - 14
public function countNumberPrintedForCategory(Category $category)
{
return $this->createQueryBuilder('fc')
->andWhere('fc.category = :category')
->setParameter('category', $category)
->select('SUM(fc.numberPrinted) as fortunesPrinted')
->getQuery()
->getSingleScalarResult();
}
// ... lines 24 - 26

This says: ok, you're only returning one row with one column, let me just give you that value directly. This is really handy for SUMs and COUNTs.

Refresh! Hey, we have just the number! Time to celebrate - take out the var_dump, refresh and... great success! So not only can we select specific fields instead of getting back objects, if you're selecting just one field on one row, getSingleScalarResult() is your new friend.