Symfony / Doctrine - Display products from database by category children and parent

Question

I've created self-referenced category entity, product entity with many-to-many relationship to category entity.

Example categories list:

MacBooks -MacBook Air --MacBook Air 11 --MacBook Air 13 -MacBook Pro --MacBook Pro 13

I'm getting products based on selected category.

public function getByCategory($category) { $qb = $this->createQueryBuilder('p'); $qb->leftJoin('p.categories', 'c'); $qb->where('c.url = :category'); $qb->setParameter('category', $category); return $qb->getQuery()->useQueryCache(true); }

For example product is inside a category MacBook Air 13.

So my code works only if I select the category MacBook Air 13.

But how to show products in parent category? For example in category MacBook Air I want to display products from categories MacBook Air 11 and MacBook Air 13, etc...

Same in category MacBooks display everything from MacBook Air, MacBook Air 11, MacBook Air 13, etc...?

Question simplification: How to get all products from all children.

MacBook -> MacBook Air -> MacBook Air 11, MacBook Air 13

Snegirekk · Accepted Answer · 2017-11-25 17:34:12Z

You can try one thing. Get all children and parents of given category first, then use where...in in your query builder. We can do it with recursive calls.

YourController.php:

public function someAction(int $id) { // ... $category = $em->getRepository('YourBundle:Category')->find($id); $categories = $this->getAllCategories($category); // OR // $categories = $this->getAllChildren($category); $products = $em->getRepository('YourBundle:Product')->getByCategories($categories); // ... } private function getAllCategories(Category $category) { return array_merge( $this->getAllChildren($category), $this->getAllParents($category) ); } private function getAllChildren(Category $category) { static $categories = array(); $categories[] = $category->getId(); if(!$category->getChildren()->isEmpty()) { foreach($category->getChildren() as $child) { $this->getAllChildren($child); } } return $categories; } private function getAllParents(Category $category) { static $categories = array(); if($category->getParent()) { $categories[] = $category->getParent()->getId(); $this->getAllParents($category->getParent()); } return $categories; }

ProductRepository.php:

// ... public function getByCategories(array $categories) { $qb = $this ->createQueryBuilder('p') ->leftJoin('p.categories', 'c'); $qb->where($qb->expr()->in('c.id', $categories)); return $qb->getQuery()->getResult(); } // ...

So we can get all products from category and all its children and parents or just from category and all its children.

Hope it helps.

Glad to help you. I'm sure you know what you can move this logic from controller to some service. It would be a better practice.

Collectives™ on Stack Overflow

Symfony / Doctrine - Display products from database by category children and parent

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related