> Symfony 6 > Go Pro with Doctrine Queries
Login to bookmark this video
Buy Access to Course
08.

Selecting Specific Fields

|

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

Let's add more stuff to this page! How about the average number of fortune cookies printed for this category? To do that, head back to our query: it lives in countNumberPrintedForCategory().

80 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')
->select('SUM(fortuneCookie.numberPrinted) AS fortunesPrinted')
->andWhere('fortuneCookie.category = :category')
->setParameter('category', $category)
->getQuery()
->getSingleScalarResult();
return (int) $result;
}
// ... lines 36 - 78
}

SELECTing the AVG

To get the average, we could add a comma then use the AVG() function. Or we can use addSelect()... which looks a bit better to me. We want the AVG() of fortuneCookie.numberPrinted aliased to fortunesAverage.

This time, I did not use the word AS... just to demonstrate that the word AS is optional. In fact, the entire fortunesAverage or AS fortunesPrinted part is optional. But by giving each a name, we can control the keys in the final result array, which we'll see in a minute.

81 lines | src/Repository/FortuneCookieRepository.php
// ... lines 1 - 24
public function countNumberPrintedForCategory(Category $category): int
{
$result = $this->createQueryBuilder('fortuneCookie')
// ... line 28
->addSelect('AVG(fortuneCookie.numberPrinted) fortunesAverage')
// ... lines 30 - 35
}
// ... lines 37 - 81

While we're here, instead of printing out the name from the $category object, let's see if we can grab the category name right inside this query. I'll say ->addSelect('category.name').

If you see a problem with this, you're right! But let's ignore that and forge ahead blindly! dd($result) at the bottom.

83 lines | src/Repository/FortuneCookieRepository.php
// ... lines 1 - 24
public function countNumberPrintedForCategory(Category $category): int
{
$result = $this->createQueryBuilder('fortuneCookie')
// ... lines 28 - 29
->addSelect('category.name')
// ... lines 31 - 34
dd($result);
// ... lines 36 - 37
}
// ... lines 39 - 83

Previously, this returned only the integer fortunesPrinted. But now, we're selecting three things. So what will it return now?

The answer is... a gigantic error!

'category' is not defined.

Yup - I referenced category... but we never joined over to it. Let's add that. We're querying from the FortuneCookie entity, and it has a category property, which is a ManyToOne. So we're joining over to one object. Do that with ->innerJoin() passing fortuneCookie.category and giving it the alias category.

84 lines | src/Repository/FortuneCookieRepository.php
// ... lines 1 - 24
public function countNumberPrintedForCategory(Category $category): int
{
$result = $this->createQueryBuilder('fortuneCookie')
// ... lines 28 - 30
->innerJoin('fortuneCookie.category', 'category')
// ... lines 32 - 38
}
// ... lines 40 - 84

Returning Multiple Columns of Results

If we go refresh the page now... this is the error I was expecting:

The query returned a row containing multiple columns.

This ->getSingleScalarResult() is perfect when you're returning a single row and a single column. As soon as you return multiple columns, ->getSingleScalarResult() won't work. To fix that, change to ->getSingleResult().

84 lines | src/Repository/FortuneCookieRepository.php
// ... lines 1 - 24
public function countNumberPrintedForCategory(Category $category): array
{
$result = $this->createQueryBuilder('fortuneCookie')
// ... lines 28 - 34
->getSingleResult();
// ... lines 36 - 38
}
// ... lines 40 - 84

This basically says:

Give me the one row of data from the database.

Try this again. That's what we want! It returns the exact three columns we selected!

And now... we need to change this method a bit. Update the int return to an array... and, down here, take off the (int) entirely and return $result. We can also remove the dd()... and you could put the return up here if you wanted to.

83 lines | src/Repository/FortuneCookieRepository.php
// ... lines 1 - 24
public function countNumberPrintedForCategory(Category $category): array
{
// ... lines 27 - 36
return $result;
}
// ... lines 39 - 83

Updating our Project to use the Results

Our method is good to go! Now let's fix the controller. This $fortunesPrinted isn't right anymore. Change it to $result instead. Then... read that out below with - $result['fortunesPrinted']. Copy that, paste, and send a fortunesAverage variable to the template set to the fortunesAverage key. Also pass categoryName set to $result['name'].

47 lines | src/Controller/FortuneController.php
// ... lines 1 - 12
class FortuneController extends AbstractController
{
// ... lines 15 - 30
public function showCategory(int $id, CategoryRepository $categoryRepository, FortuneCookieRepository $fortuneCookieRepository): Response
{
// ... lines 33 - 38
return $this->render('fortune/showCategory.html.twig',[
'category' => $category,
'fortunesPrinted' => $result['fortunesPrinted'],
'fortunesAverage' => $result['fortunesAverage'],
'categoryName' => $result['name'],
]);
}
}

Template time! Over in showCategory.html.twig, we have access to the entire $category object... which is how we're printing category.name. But now, we also have a categoryName variable. Replace category.name with categoryName.

40 lines | templates/fortune/showCategory.html.twig
// ... lines 1 - 2
{% block body %}
// ... lines 4 - 5
<h1 class="text-3xl p-5 text-center my-4 font-semibold"><span class="fa {{ category.iconKey }}"></span> {{ categoryName }} Fortunes</h1>
// ... lines 7 - 38
{% endblock %}

There's... no actual reason to do that - I'm just proving that we are able to grab extra data in our new query. Though, if we had also selected iconKey, then we could potentially avoid querying for the Category object entirely. However, while that might make our page a tiny bit faster, it's almost definitely overkill and makes our code more confusing. Using objects is best!

Ok, below, for the "Print History", hit "enter" and add {{ fortunesAverage|number_format }} then average.

40 lines | templates/fortune/showCategory.html.twig
// ... lines 1 - 2
{% block body %}
// ... lines 4 - 9
<thead class="bg-slate-500 text-white">
// ... lines 11 - 14
<th class="border p-4">
Print History ({{ fortunesPrinted|number_format }} total, {{ fortunesAverage|number_format }} average)
</th>
// ... line 18
</thead>
// ... lines 20 - 38
{% endblock %}

Awesome. Try this again! If I didn't make any mistakes... got it! Everything works! We have two queries: one for the category that's joined over to fortune_cookies and the one that we just made that grabs the SUM, AVG, and the name also with a JOIN. Love it!

Getting full entity objects back from Doctrine is the ideal situation because... objects are just really nice to work with. But at the end of the day, if you need to query for specific data or columns, you can totally do that. And as we just saw, Doctrine will return an associative array.

However, we can go one step further and ask Doctrine to return this specific data inside an object. Let's talk about that next.