John Davidson

Message:

I'm doing a project on symfony 6 and I have some problems with my request!
I have a menu + subcategory with products.
The products are only listed in the subcategories
So, I created this request with my repository

/**

 * I need 5 articles with the same category

 * @return Articles[] Returns an array of Articles objects

 */

public function FindTheLastFiveArticleWithCategory(): array

{

    return $this->createQueryBuilder('a')

        ->setMaxResults(5)

        ->getQuery()

        ->getResult()

        ;

}

and my controller

$GroupeCategory = [];

$ArticlesCategory = $this->articlesRepository->FindTheLastFiveArticleWithCategory();

 

foreach ($ArticlesCategory as $cat) {

    $id = $cat->getCatagory()[0]->getId();

    if (isset($GroupeCategory[$id])) {

        $GroupeCategory[$id]['articles'][] = $cat;

    } else {

        $GroupeCategory[$id] = [

            'id'            => $id,

            'category'      => $cat->getCatagory()[0]->getName(),

            'slugCategory'  => $cat->getCatagory()[0]->getSlug(),

            'articles'      => [

                $cat

            ]

        ];

    }

}

Until then, everything works.

BUT
The final result should have been

I want 5 articles / main category

of the type

SELECT all my items from the TABLE items

WHERE

SELECT all my items from the table item WHERE product name = short sleeve shirt limit 5

SELECT all my items from the table item WHERE product name = jeans *** limit 5

SELECT all my items from the table item WHERE product name = watch *** limit 5

---

My Answer:
To achieve the desired result of getting the last 5 articles for each main category, you can modify your repository method to include a join with the category entity and group by the category. Here's an example of how you can modify your repository method:

php
/**
* Get the last 5 articles for each main category
* @return array
*/
public function findLastFiveArticlesForMainCategories(): array
{
return $this->createQueryBuilder('a')
->leftJoin('a.category', 'c')
->select('c.name as categoryName, c.slug as categorySlug, a')
->orderBy('a.createdAt', 'DESC')
->groupBy('c.id')
->setMaxResults(5)
->getQuery()
->getResult();
}


Then in your controller, you can iterate over the results to group the articles by main category:

php
$mainCategories = [];
$articlesByCategory = $this->articlesRepository->findLastFiveArticlesForMainCategories();

foreach ($articlesByCategory as $article) {
$categoryName = $article['categoryName'];
$categorySlug = $article['categorySlug'];

if (!isset($mainCategories[$categoryName])) {
$mainCategories[$categoryName] = [
'name' => $categoryName,
'slug' => $categorySlug,
'articles' => []
];
}

$mainCategories[$categoryName]['articles'][] = $article['a'];
}

// Now $mainCategories will contain the last 5 articles for each main category


This way, you will get the last 5 articles for each main category and be able to group them accordingly in your controller.