CODEWITHSUNDEEP
symfonydoctrine
kironet
kironet

Reputation: 935

Symfony / Doctrine - GroupBy is not returning all results

I have a problem with GroupBy.

I have entity with categories and entity with products. I want to get all the products grouped by category(id).

public function getAll()
{
    $qb = $this->createQueryBuilder('p');
    $qb->leftJoin('p.categories', 'category');
    $qb->where('p.enabled = true');
    $qb->andWhere('p.visible = true');
    $qb->andWhere('p.quantity >= 1');
    $qb->groupBy('category.id');

    return $qb->getQuery()->getResult();
}

But this returns just few records(I think 1 from each category).

How can I get all products grouped in categories?

Thanks

Upvotes: 0

Views: 283

Answers (1)

Dan Costinel
Dan Costinel

Reputation: 1736

So if you have something like this:

Category(id, name) 1-N Products (id, name, enabled, visible, quantity, category_id), and:

categories:
id  name
1   category1
2   category2

products:
id  name    enabled  visible  quantity  category_id
1   prod1   1        1        1         1
2   prod2   1        1        2         1
3   prod3   1        1        3         1
4   prod4   1        1        1         2
5   prod5   1        1        2         2

Then I think you should start from CategoryRepository, and:

public function getAll()
{
    $qb = $this->createQueryBuilder('c');
    $qb
        ->select('c.name')
        ->addSelect("(SELECT GROUP_CONCAT(p.name) FROM AppBundle:Product p WHERE p.category = c.id AND p.enabled = :enabled AND p.visible = :visible AND p.quantity >= :number) AS prods")
        ->setParameter('enabled', true)
        ->setParameter('visible', true)
        ->setParameter('number', 1)
    ;
    return $qb->getQuery()->getResult();
}

The results will look like:

array:2 [▼
    0 => array:2 [▼
        "name" => "category1"
        "prods" => "prod1,prod2,prod3"
    ]
    1 => array:2 [▼
        "name" => "category2"
        "prods" => "prod4,prod5"
    ]
]

Also, you'll need to install beberlei's DoctrineExtensions to let doctrine know about GROUP_CONCAT function

Upvotes: 1

Related Questions