How to query for all sub-categories containing a product (directly in sub-category, or anywhere down the category-tree, eg. in sub-sub category)?

Question

Schema:

Category [ id, parent, children, left, right, level, root, products ]

Product [ id, category, marketSegments ]

MarketSegment [ id ]

Also each entity has fields like name, description, slug but those are not relevant to my issue.

Example data

Legend:

c - Category, p - Product

Products marked with * are tagged with market segment "Export to USA"

Food                                [c, id: 1, level: 0]
---> Vegetables                       [c, id: 2, level: 1]
--------------> Potato                  [p, id: 1]
--------------> Carrot                  [p, id: 2]
---> Fruits                           [c, id: 3, level: 1]
--------------> Berries                 [c, id: 5, level: 2]
---------------------------> Grapes       [p, id: 3]
--------------> Hesperidiums            [c, id: 6, level: 2]
---------------------------> Orange*      [p, id: 4]
---> Meat                             [c, id: 4, level: 1]
--------------> Beef*                   [p, id: 5]

Expected query result:

For given data and $category = Food, $marketSegment = Export to USA the expected result would be:

$filteredCategories = [Fruits, Meat]

Why?

• Meat becouse it contains product Beef which is tagged with Export to USA
• Fruits becouse it contains category Hesperidiums which contains a tagged product.

It does not matter how deep in the nested tree the category containing tagged product is.

This:

Sports [c]
-----> Individual [c]
----------------> Fight [c]
----------------------> MMA [c]
--------------------------> Boxing gloves* [p]

For $category = Sports should return: [ Individual ].

For $category = Fight should return: [ MMA ].

---

My (not working) DQL approach:

SELECT DISTINCT cat FROM Avocode\CatalogBundle\Entity\Category cat 
WHERE cat.parent = :parent_id 
AND (EXISTS(
        SELECT sub FROM Avocode\CatalogBundle\Entity\Category sub 
        LEFT JOIN sub.products prod 
        LEFT JOIN prod.marketSegments seg 
        WHERE sub.left > cat.left 
        AND sub.right < cat.right 
        AND seg.id = :segment_id
)) 
ORDER BY cat.root, cat.left ASC

Answer 1

NestedTreeRepository that ships with the Gedmo NestedSet behavior extension has a method getChildrenQueryBuilder() that allows you to query for children given a parent node, and has a parameter for querying for nodes that are several layers deep in the hierarchy. You should investigate methods and options that ship with NestedTreeRepository so as not need to build the DQL by hand.

I'm not entirely sure how all of your code is setup so I can't offer an exact solution but I think you can figure it out :)

NestedSet documentation: http://www.gediminasm.org/article/tree-nestedset-behavior-extension-for-doctrine-2