Utilizar GROUP BY para buscar y contar en una sola consulta

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

Un último reto. En la página de inicio, tenemos 7 consultas. Una para obtener las categorías... y 6 más para obtener el recuento de galletas de la suerte de cada una de esas 6 categorías.

Tener 7 consultas no es... probablemente un problema... y no deberías preocuparte por optimizar el rendimiento hasta que realmente veas que hay un problema. Pero desafiémonos a convertir esas 7 consultas en una sola.

Pensemos: podríamos consultar todas las categorías, JOIN hasta las galletas de la suerte relacionadas, GROUP BY la categoría y, a continuación, COUNT las galletas de la suerte. Si eso no tiene sentido, no te preocupes. Lo veremos en acción.

Utilizar un Grupo Por Para Seleccionar un Objeto + Otros Datos

Dirígete a FortuneController. Estamos en la página principal, y estamos utilizando el métodofindAllOrdered() de $categoryRepository. Ve a buscar ese método... aquí está. Ya estamos seleccionando desde category. Ahora también->addSelect('COUNT(fortuneCookie.id) AS fortuneCookiesTotal'). Para unirnos y conseguir ese alias fortuneCookie, añade ->leftJoin('category.fortuneCookies'), luegofortuneCookie. Por último, para que este COUNT funcione correctamente, di->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
                            
            }

Bien, ¡veamos qué obtenemos! Aquí abajo, 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

Antes, esto devolvía un array de objetos Category. Si refrescamos... es una matriz, pero ahora es una matriz de matrices donde la clave 0 es el objeto Category, y luego tenemos este fortuneCookiesTotal extra . Así que... ¡seleccionó exactamente lo que queríamos! Pero... cambió la estructura subyacente. Y tenía que hacerlo, ¿no? Tenía que darnos de alguna manera el objeto Category y la columna extra entre bastidores.

Elimina la sentencia dd. Esto sigue devolviendo un array... pero elimina el@return porque ya no devuelve una matriz de objetos Category. También podríamos actualizarlo a un phpdoc más elegante que describa la nueva estructura.

A continuación, para tener en cuenta el nuevo retorno, dirígete a homepage.html.twig. Estamos haciendo un bucle sobre category in categories... que ahora no es del todo correcto: la categoría está en este índice 0. Cámbialo por for categoryData in categories... y dentro añade set category = categoryData[0]. Es feo, pero hablaremos de ello más adelante.

            
            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 %}

Desplázate hasta length. En lugar de buscar a través de la relación -lo que funcionaría, pero provocaría consultas adicionales- utilizacategoryData.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

Hagamos esto Actualiza y... ¡sólo una consulta! ¡Guau!

La fea estructura de datos

Lo peor de esto es que la estructura de nuestros datos ha cambiado... y ahora tenemos que leer esta fea clave 0. No lo haré ahora, pero una solución mejor sería aprovechar un objeto DTO para contener esto. Por ejemplo, podríamos crear una nueva clase llamada CategoryWithFortuneCount con dos propiedades: $category y$fortuneCount. En este método del repositorio, podríamos hacer un bucle sobre $query->getResults()y crear un objeto CategoryWithFortuneCount para cada uno. Al final, nuestro método devolvería una matriz de CategoryWithFortuneCount. Devolver una matriz de objetos es mucho mejor que una matriz de matrices... con algún índice aleatorio 0.

Arreglar la página de búsqueda

Hablando de esa estructura cambiada, si buscamos algo... obtenemos un error:

Imposible acceder a una clave "0" en un objeto de la clase Category.

Es... esta línea de aquí. Cuando buscamos algo, utilizamos el método search()y... ¡sorpresa! Ese método no tiene los nuevos addSelect() ygroupBy(): sigue devolviendo una matriz de objetos Category.

            
            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
                            
            }

Para solucionarlo, crea un private function aquí abajo que pueda contener el grupo por:addGroupByCategory(QueryBuilder $qb) y devolverá un QueryBuilder. Ah, y haz que el argumento sea opcional... entonces crea un nuevo constructor de consultas si no tenemos ninguno.

            
            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

Vale, sube y roba la lógica - el ->addSelect(), ->leftJoin(), y->addGroupBy(). Pégalo aquí abajo. Ah, y addGroupByCategory() no es un buen nombre: utiliza 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

Fantástico. Arriba, ¡simplifica! Cambia esto por addGroupByCategoryAndCountFortunes()... y entonces no necesitaremos los ->addGroupBy(), ->leftJoin(), o ->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

Para asegurarnos de que esa parte funciona, gira y... vuelve a la página de inicio. Eso tiene buena pinta... pero si avanzamos... sigue roto. Abajo en search()añade $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

Y ahora... otro error:

fortuneCookie ya está definido.

¡Vaya! Pero, sí, tiene sentido. Estamos uniendo en nuestro nuevo método... y también enaddFortuneCookieJoinAndSelect(). Afortunadamente, ya no necesitamos esta segunda llamada: estábamos uniendo y seleccionando para resolver el problema N+1... pero ahora tenemos una consulta aún más avanzada para hacerlo. Copia nuestro nuevo método, bórralo y pégalo sobre el antiguo.

            
            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

Y ahora... ¡ya está! ¡Sólo 1 consulta!

Amigos, ¡lo hemos conseguido! ¡Guau! Gracias por acompañarme en este viaje mágico a través de todas las cosas de Doctrine Query. Estas cosas son raras, geniales y divertidas. Espero que lo hayas disfrutado tanto como yo. Si te encuentras con alguna situación loca en la que no hayamos pensado, tienes alguna pregunta o fotos de tu gato, siempre estamos aquí para ti en los comentarios. Bueno, ¡hasta la próxima!

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

Hazte profesional con las consultas de Doctrine

Utilizar GROUP BY para buscar y contar en una sola consulta

Keep on Learning!

Utilizar un Grupo Por Para Seleccionar un Objeto + Otros Datos

La fea estructura de datos

Arreglar la página de búsqueda

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