Flexible search for categories included product

Question

I tried to create a flexible search for depending on a specific category.

What I am trying to do, attract products of supercategory code '3741' but products must only belong to this category and no subcategories or supercategories.

There is my flexible query :

select {prod.pk}, {prod.code}, {cat.code},{cat.name} from
 {Product as prod
     join CategoryProductRelation as level3 on {level3.target} = {prod.pk}
     join CategoryCategoryRelation as level2 on {level2.target} = {level3.source}
     join CategoryCategoryRelation as level1 on {level1.target} = {level2.source}
     join Category as cat on {cat.pk} = {level1.source}
 }
 where {cat.pk} = '8796117467278'

This query brings to all products, if product has my category.

Already thanks for your answers.

Please provide sample data and desired results. – Gordon Linoff Dec 28 '20 at 11:50 — Gordon Linoff, Dec 28 '20 at 11:50

score 1 · Answer 1 · edited May 19 '22 at 14:18

1

Below Query can be further modified according to requirement.

SELECT {p:code} product, {c:code} Category FROM {Product AS p JOIN CatalogVersion AS cv ON {p:catalogVersion} = {cv:pk} JOIN CategoryProductRelation as cpr ON {cpr:target}={p:pk} JOIN Category as c ON {c:pk}={cpr.source}} 
WHERE {cv:version} = 'Online' AND {p:code}=100029544

edited May 19 '22 at 14:18

Suraj Rao

29,388
11
94
103

answered May 19 '22 at 14:17

Muhammad Hamza Naeem

26
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 20 '22 at 00:32

Flexible search for categories included product

1 Answers1