Utilizar GROUP BY para buscar y contar en una sola consulta

Hey Braunstetter,

We're really happy to hear it was useful for you ;) Thanks for the feedback!

Cheers!

Thank you, for you feedback! This is the best assessment of what we do!

I went and tried it myself... just for the homepage, not for the search.

src/Model/CategoryWithFortuneCount (new file)

<?php

namespace App\Model;

use App\Entity\Category;

class CategoryWithFortuneCount
{
    public function __construct(
        public Category $category,
        public int $fortuneCount
    ) {}
}

src/Repository/CategoryRepository (added new method, didn't bother abstracting QueryBuilder methods for simplicity)
... did I get the PHPDoc right?

/**
 * @return CategoryWithFortuneCount[]
 */
public function findAllOrderedWithStats(): array
{
    $results = $this->createQueryBuilder('category')
        ->addSelect('COUNT(fortuneCookie.id) AS fortuneCount')
        ->leftJoin('category.fortuneCookies', 'fortuneCookie')
        ->addGroupBy('category.id')
        ->addOrderBy('category.name', Criteria::DESC)
        ->getQuery()
        ->getResult();

    $return = [];
    foreach ($results as $result) {
        $return[] = new CategoryWithFortuneCount(...$result);
    }
            
    return $return;
}

/src/Controller/FortuneController.php (modified index method, and again I didn't hook search up so ignore that)

public function index(Request $request, CategoryRepository $categoryRepository): Response
{
    $searchTerm = $request->query->get('q');
    if ($searchTerm) {
        $categoriesWithFortuneCount = $categoryRepository->search($searchTerm); // IGNORE THIS
    } else {
        $categoriesWithFortuneCount = $categoryRepository->findAllOrderedWithStats(); // Array of DTO Models
    }

    return $this->render('fortune/homepage.html.twig',[
        'categoriesWithFortuneCount' => $categoriesWithFortuneCount
    ]);
}

/templates/fortune/homepage.html.twig (modified, just the looping part)

{% for categoryWithFortuneCount in categoriesWithFortuneCount %}
    <a class="bg-orange-400 hover:bg-orange-500 text-white text-center rounded-full p-4" href="{{ path('app_category_show', {'id':  categoryWithFortuneCount.category.id}) }}">
        <span class="fa {{ categoryWithFortuneCount.category.iconKey }}"></span> <span class="font-bold text-lg">{{ categoryWithFortuneCount.category.name }}</span>  ({{ categoryWithFortuneCount.fortuneCount }})
    </a>
{% else %}
    <h3>Hmm... there don't seem to be any fortunes</h3>
{% endfor %}

It ... appears to work.

Nicely done @Ryan-L - thank you for sharing it
And yea, you got the PHPDoc right

Cheers!

Hey @Ryan-L

You can try it yourself :D - but yea, it would give you the "fortuneCount" and the Category entity under a numeric index of the array

Cheers!

Thank you for your feedback!

Cheers!

Hey Peter,

We're happy to hear this course was helpful for you! :)

Cheers!

Hey Arthur-E,

Thank you for your interest in SymfonyCasts tutorials!

We didn't cover Doctrine's low-level features like UnitOfWork in this course on purpose because it's out of scope here. We're mostly talking about writing complex custom DB queries leveraging Doctrine while UnitOfWork is more about operations and hooks. But yeah, I understand you! The topic around UnitOfWork is also interesting and complex, but it would be a good topic for a different course. I will add this topi to our ideas pool, but I can't give you any estimations when it might be released, too many good topics are prioritized for now. Btw, we talked a little bit about UnitOfWork in different tutorials and you may leverage our advanced search to find some related videos: https://symfonycasts.com/search?q=UnitOfWork&sort=most relevant&page=1&types=video

I hope that helps!

Cheers!

Sure thing, guys! 100% agree!

Regarding the timing issue, though, if the course would be available within a year or so that totally works for me in particular... There is no rush whatsoever on the matter it would be just lovely to have it here as well...

Thank's for the hint! =)

Appreciate it!

Hey Arthur,

Great, thanks for clarifying it! I added that topic to the list, but unfortunately, I can't guarantee it will appear within a year, it totally depends on the releasing vector and course authors' availability. But we will try to do our best. Thanks for understanding!

Cheers!

Thank you!!! We are happy to know that!

Cheers and happy coding!