When a product falls into multiple categories, in some cases it is desirable to use only the (lowest) subcategory of the category tree. This is called the deepest category.
You can then add that attribute to the search properties so that products are found when a category is searched. The deepest category can also be used as an additional filter. This is often done in the template used after a search. It is then convenient to easily have all categories containing the found products listed together.
For this, a derived attribute of the Advanced type is required.
If you would like to use this, please contact Tweakwise Support: [email protected].
The query looks as follows:
SELECT pc.CategorieId FROM TB_Koppel_Product_Categorie pc INNER JOIN TB_Categorie_Tree t on t.ParentID = pc.CategorieID WHERE t.CategorieID IN (SELECT CategorieID FROM TB_Koppel_Product_Categorie
WHERE Artikelnummer = [Product].ArtikelNummer) and pc.Artikelnummer = [Product].Artikelnummer ) THEN [Categorie].Naam END AS Waarde, 1 AS Volgorde