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

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

Solution

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.