Utilizar GROUP BY para buscar y contar en una sola consulta
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.
With a Subscription, click any sentence in the script to jump to that part of the video!
Login SubscribeUn ú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').
| // ... 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()).
| // ... 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.
| // ... 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.
| // ... 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.
| // ... 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.
| // ... 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().
| // ... 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().
| // ... 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).
| // ... 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:
fortuneCookieya 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.
| // ... 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!
Thank you team. It's awesome that I can learn new stuff even after all these years. Great work!