Using GROUP BY to Fetch & Count in 1 Query

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

One last challenge. On the homepage, we have seven queries. That's one to fetch the categories... and 6 more to get the fortune cookie count for each of those 6 categories.

Having 7 queries is... probably not a problem... and you shouldn't worry about optimizing performance until you actually see that there is a problem. But let's challenge ourselves to turn these seven queries into one.

Let's think: we could query for all the categories, JOIN over to the related fortune cookies, GROUP BY the category, and then COUNT the fortune cookies. If that doesn't make sense, no worries. We'll see it in action.

Using a Group By To Select an Object + Other Data

Head over to FortuneController. We're on the homepage, and we're using the findAllOrdered() method from $categoryRepository. Go find that method... here it is. We're already selecting from category. Now also ->addSelect('COUNT(fortuneCookie.id) AS fortuneCookiesTotal'). To join and get that fortuneCookie alias, add ->leftJoin('category.fortuneCookies'), then fortuneCookie. Finally, for this COUNT to work correctly, say ->addGroupBy('category.id').

            
            125 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... lines 1 - 18
                            
            class CategoryRepository extends ServiceEntityRepository
        
            {
        
                                                    ... lines 21 - 28
                            
                public function findAllOrdered(): array
        
                {
        
                                                    ... line 31
                            
                    $qb = $this->createQueryBuilder('category')
        
                        ->addOrderBy('category.name', Criteria::DESC)
        
                        ->addSelect('COUNT(fortuneCookie.id) AS fortuneCookiesTotal')
        
                        ->leftJoin('category.fortuneCookies', 'fortuneCookie')
        
                        ->addGroupBy('category.id');
        
                                                    ... lines 37 - 40
                            
                }
        
                                                    ... lines 42 - 123
                            
            }

Okay, let's see what we get! Down here, dd($query->getResult()).

            
            125 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... lines 1 - 28
                            
                public function findAllOrdered(): array
        
                {
        
                                                    ... lines 31 - 36
                            
                    $query = $qb->getQuery();
        
                    dd($query->getResult());
        
                                                    ... lines 39 - 40
                            
                }
        
                                                    ... lines 42 - 125

Previously, this returned an array of Category objects. If we refresh... it is an array, but it's now an array of arrays where the 0 key is the Category object, and then we have this extra fortuneCookiesTotal. So... it selected exactly what we wanted! But... it changed the underlying structure. And it kind of had to, right? It needed to somehow give us the Category object and the extra column behind the scenes.

Remove the dd statement. This still returns an array... but remove the @return because it no longer returns an array of Category objects. We could also update that to some fancier phpdoc that describes the new structure.

Next, to account for the new return, head to homepage.html.twig. We're looping over category in categories... which isn't quite right now: the category is on this 0 index. Change this to say for categoryData in categories... then inside add set category = categoryData[0]. It's ugly, but more on that in a minute.

            
            18 lines
            
            templates/fortune/homepage.html.twig
        
                                                    ... lines 1 - 2
                            
            {% block body %}
        
                                                    ... lines 4 - 7
                            
                {% for categoryData in categories %}
        
                    {% set category = categoryData[0] %}
        
                                                    ... lines 10 - 14
                            
                {% endfor %}
        
                                                    ... line 16
                            
            {% endblock %}

Scroll over to the length. Instead of reaching across the relationship - which would work, but would trigger extra queries - use categoryData.fortuneCookiesTotal.

            
            18 lines
            
            templates/fortune/homepage.html.twig
        
                                                    ... lines 1 - 7
                            
                {% for categoryData in categories %}
        
                                                    ... line 9
                            
                    <a class="bg-orange-400 hover:bg-orange-500 text-white text-center rounded-full p-4" href="{{ path('app_category_show', {'id':  category.id}) }}">
        
                        <span class="fa {{ category.iconKey }}"></span> <span class="font-bold text-lg">{{ category.name }}</span>  ({{ categoryData.fortuneCookiesTotal }})
        
                    </a>
        
                                                    ... lines 13 - 14
                            
                {% endfor %}
        
                                                    ... lines 16 - 18

Let's do this! Refresh and... just one query! Woo!

The Ugly Data Structure

The worst part about this is that the structure of our data changed... and now we have to read this ugly 0 key. I won't do it now, but a better solution would be to leverage a DTO object to hold this. For example, we might create a new class called CategoryWithFortuneCount with two properties - $category and $fortuneCount. In this repository method, we could loop over $query->getResults() and create a CategoryWithFortuneCount object for each one. Ultimately, our method would return an array of CategoryWithFortuneCount. Returning an array of objects is much nicer than an array of arrays... with some random 0 index.

Fixing the Search Page

Speaking of that changed structure, if we search for something... we get an error:

Impossible to access a key "0" on an object of class Category.

It's... this line right here. When we search for something, we use the search() method and... surprise! That method doesn't have the new addSelect() and groupBy(): it still returns an array of Category objects.

            
            121 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... lines 1 - 18
                            
            class CategoryRepository extends ServiceEntityRepository
        
            {
        
                                                    ... lines 21 - 38
                            
                /**
        
                 * @return Category[]
        
                 */
        
                public function search(string $term): array
        
                {
        
                                                    ... lines 44 - 52
                            
                }
        
                                                    ... lines 54 - 119
                            
            }

To fix that, create a private function down here that can hold the group by: addGroupByCategory(QueryBuilder $qb) and it'll return a QueryBuilder. Oh, and make the argument optional... then create a new query builder if we don't have one.

            
            126 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... lines 1 - 79
                            
                private function addGroupByCategory(QueryBuilder $qb = null): QueryBuilder
        
                {
        
                    return ($qb ?? $this->createQueryBuilder('category'))
        
                                                    ... lines 83 - 85
                            
                }
        
                                                    ... lines 87 - 126

Ok, head up and steal the logic - the ->addSelect(), ->leftJoin(), and ->addGroupBy(). Paste that down here. Oh, and addGroupByCategory() isn't a great name: use addGroupByCategoryAndCountFortunes().

            
            124 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... lines 1 - 77
                            
                private function addGroupByCategory(QueryBuilder $qb = null): QueryBuilder
        
                {
        
                    return ($qb ?? $this->createQueryBuilder('category'))
        
                        ->addSelect('COUNT(fortuneCookie.id) AS fortuneCookiesTotal')
        
                        ->leftJoin('category.fortuneCookies', 'fortuneCookie')
        
                        ->addGroupBy('category.id');
        
                }
        
                                                    ... lines 85 - 124

Awesome. Above, simplify! Change this to addGroupByCategoryAndCountFortunes()... and then we don't need the ->addGroupBy(), ->leftJoin(), or ->addSelect().

            
            124 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... lines 1 - 25
                            
                public function findAllOrdered(): array
        
                {
        
                                                    ... line 28
                            
                    $qb = $this->addGroupByCategory()
        
                        ->addOrderBy('category.name', Criteria::DESC);
        
                                                    ... lines 31 - 33
                            
                }
        
                                                    ... lines 35 - 124

To make sure that part is working, spin over and... head back to the homepage. That looks good... but if we go forward... still broken. Down in search() add $qb = $this->addGroupByCategoryAndCountFortunes($qb).

            
            124 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... lines 1 - 35
                            
                public function search(string $term): array
        
                {
        
                                                    ... line 38
                            
                    $qb = $this->addOrderByCategoryName();
        
                    $qb = $this->addGroupByCategory($qb);
        
                                                    ... line 41
                            
                    return $this->addFortuneCookieJoinAndSelect($qb)
        
                                                    ... lines 43 - 46
                            
                        ->getResult();
        
                }
        
                                                    ... lines 49 - 124

And now... another error:

fortuneCookie is already defined.

Darn! But, yea, that makes sense. We're joining in our new method... and also in addFortuneCookieJoinAndSelect(). Fortunately, we don't need this second call at all anymore: we were joining and selecting to solve the N+1 problem... but now we have an even more advanced query to do that. Copy our new method, delete, then paste it over the old one.

            
            123 lines
            
            src/Repository/CategoryRepository.php
        
                                                    ... lines 1 - 35
                            
                public function search(string $term): array
        
                {
        
                                                    ... lines 38 - 40
                            
                    return $this->addGroupByCategory($qb)
        
                                                    ... lines 42 - 46
                            
                }
        
                                                    ... lines 48 - 123

And now... got it! Only 1 query!

Yo friends, we did it! Woo! Thanks for joining me on this magical ride through all things Doctrine Query. This stuff is just weird, cool and fun. I hope you enjoyed it as much as I did. If you encounter any crazy situation that we haven't thought about, have any questions, or pictures of your cat, we're always here for you down in the comments. Alright, see you next time!

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

TRACK

Symfony 6 >

COURSE

Go Pro with Doctrine Queries

Using GROUP BY to Fetch & Count in 1 Query

Keep on Learning!

Using a Group By To Select an Object + Other Data

The Ugly Data Structure

Fixing the Search Page

Leave a comment!

What PHP libraries does this tutorial use?

	... lines 1 - 18
	class CategoryRepository extends ServiceEntityRepository
	{
	... lines 21 - 28
	public function findAllOrdered(): array
	{
	... line 31
	$qb = $this->createQueryBuilder('category')
	->addOrderBy('category.name', Criteria::DESC)
	->addSelect('COUNT(fortuneCookie.id) AS fortuneCookiesTotal')
	->leftJoin('category.fortuneCookies', 'fortuneCookie')
	->addGroupBy('category.id');
	... lines 37 - 40
	}
	... lines 42 - 123
	}

	... lines 1 - 28
	public function findAllOrdered(): array
	{
	... lines 31 - 36
	$query = $qb->getQuery();
	dd($query->getResult());
	... lines 39 - 40
	}
	... lines 42 - 125

	... lines 1 - 2
	{% block body %}
	... lines 4 - 7
	{% for categoryData in categories %}
	{% set category = categoryData[0] %}
	... lines 10 - 14
	{% endfor %}
	... line 16
	{% endblock %}

	... lines 1 - 7
	{% for categoryData in categories %}
	... line 9
	<a class="bg-orange-400 hover:bg-orange-500 text-white text-center rounded-full p-4" href="{{ path('app_category_show', {'id': category.id}) }}">
	<span class="fa {{ category.iconKey }}"></span> <span class="font-bold text-lg">{{ category.name }}</span> ({{ categoryData.fortuneCookiesTotal }})
	</a>
	... lines 13 - 14
	{% endfor %}
	... lines 16 - 18

	... lines 1 - 18
	class CategoryRepository extends ServiceEntityRepository
	{
	... lines 21 - 38
	/**
	* @return Category[]
	*/
	public function search(string $term): array
	{
	... lines 44 - 52
	}
	... lines 54 - 119
	}

	... lines 1 - 79
	private function addGroupByCategory(QueryBuilder $qb = null): QueryBuilder
	{
	return ($qb ?? $this->createQueryBuilder('category'))
	... lines 83 - 85
	}
	... lines 87 - 126

	... lines 1 - 77
	private function addGroupByCategory(QueryBuilder $qb = null): QueryBuilder
	{
	return ($qb ?? $this->createQueryBuilder('category'))
	->addSelect('COUNT(fortuneCookie.id) AS fortuneCookiesTotal')
	->leftJoin('category.fortuneCookies', 'fortuneCookie')
	->addGroupBy('category.id');
	}
	... lines 85 - 124

	... lines 1 - 25
	public function findAllOrdered(): array
	{
	... line 28
	$qb = $this->addGroupByCategory()
	->addOrderBy('category.name', Criteria::DESC);
	... lines 31 - 33
	}
	... lines 35 - 124

	... lines 1 - 35
	public function search(string $term): array
	{
	... line 38
	$qb = $this->addOrderByCategoryName();
	$qb = $this->addGroupByCategory($qb);
	... line 41
	return $this->addFortuneCookieJoinAndSelect($qb)
	... lines 43 - 46
	->getResult();
	}
	... lines 49 - 124

	... lines 1 - 35
	public function search(string $term): array
	{
	... lines 38 - 40
	return $this->addGroupByCategory($qb)
	... lines 42 - 46
	}
	... lines 48 - 123