Symfony / Doctrine - GroupBy is not returning all results

Question

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

Dan Costinel · Accepted Answer · 2018-05-05 14:23:32Z

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

Hi, thank you and sorry for the delayed response. I have many-to-many relationship. Should have mentioned it in my question.
I have just used the same code that I had, but in CategoryRepository and it working. Thank you for the suggestion.

Collectives™ on Stack Overflow

Symfony / Doctrine - GroupBy is not returning all results

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related