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

Symfony 6 >

COURSE

Go Pro with Doctrine Queries

Buy Access to Course

Selecting Specific Fields

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 Chapter

Next Chapter
Edit on GitHub

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.

Leave a comment!

Most Useful Newest Oldest
2
Login or Register to join the conversation
Stanislaw Avatar
Stanislaw Avatar Stanislaw | posted 29 days ago

I had an error and had to add ->groupBy('category.name') to make it work.

Reply
MolloKhan Avatar

Hey @Stanislaw

Could you tell me a bit more about your problem? I wonder what made you to add a "group by" clause

Reply
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 Chapter
Next Chapter
userVoice