Using GROUP BY to Fetch & Count in 1 Query

Thank you team. It's awesome that I can learn new stuff even after all these years. Great work!

Hey Braunstetter,

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

Cheers!

Great stuff team. Qualtity of the content get's better each time a new course arrives.

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

Man, I would have LOVED to see that custom DTO object that stores both category and fortuneCount.
In the __construct(), the fortuneCount property would be "public int $fortuneCount", but what type would category be? The Entity?

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!

Hey Ryan,

From the regular point of view I would say this course is a little advance for "Novice" level of folks who tries Symfony...
Can we dive really to the level you suggest in your title "Go Pro with Doctrine Queries"? I mean like we do have $unitOfWork where we work with "entityInsertions" | "entityUpdates" | "entityDeletions" or $metadataFactory in Doctrine and there are loads of other complex obstacles when you are working with difficult projects... Since you know professional programmers it's considerably easy for you to talk to them for 30 min each, grab several complex Doctrine issues from them and their solutions, they invented during the tackling those problems, and create a proper course here highlighting real/complex problems here... That would give a real boost for your platform since many folks would join here so as to be aware of those complex issues and develop their skill further and deeper...
No pressure) Just thoughts to consider're...

Thanks to you anyways! =)
You are doing a great job for folks!

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 for this great job!

Great course. It takes talent to make complex topics to look so simple. Keep up good work.

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

Cheers and happy coding!