How to write a query in doctrine?

Question

symfony

I work in Symfony2.
I have two entities

• Category {id, name}
  
• Item{id,category,name,isActive}
  

I generate a menu with nested item. like
Car (category.name)
- car1 (item.name)
- car2 (item.name)
Aircraft (category.name)
- aircraft1 (item.name)
- aircraft2 (item.name)
Generation in twig
{% for category in categories %}
{{category.name}}
{% for item in category.items %}
- {{ item.name }}
{% endfor %}
{% endfor %}
If you request to find everything, then there is no problem.
But I need to
1) Display all categories
2) Display nested item which isActive = 1
Tried like this

class CategoryRepository extends EntityRepository
{
    public function findAll()
    {
        return $this->createQueryBuilder('c')
            ->innerJoin('c.items', 'i')
            ->where('i.isActive = 1')
            ->orderBy('c.position', 'ASC')
            ->getQuery()->getResult();
    }

}

1) Not all categories are displayed, only those that have items
2) Items are displayed all the same, even isActive = 0
I would be grateful for the solution + links on the topic.

Answer 1

I would do this with two requests: the first one is pulling out the ID of all categories, the second one is pulling out all the categories with their items.
Pure SQL example:

SELECT DISTINCT(category_id)
FROM items
WHERE is_active = 1;

SELECT *
FROM categories c
INNER JOIN items i
ON (i.category_id = c.id)
WHERE c.id IN (categoriesIDS)
ORDER BY c.position ASC;