Unable to create Flexible Search Query

[Former Member]

Hi All ,

I want to create flexible search query to fetch all the supercategories of product "XYZ" of particular product catalog like defalut:stage

Below is the query I generate.

SELECT {p:code}, {c:code} FROM { Product as p JOIN CategoryProductRelation as rel ON {p:PK} = {rel:target} JOIN Category AS c ON {rel:source} = {c:PK} } where {p:code} = "XYZ" and {p:catalogVersion} = "8796093252185"

How I can compare catalog using id and version instead of its PK, because I want to use it on multiple system?

Thanks, Rasika

[Former Member]

You can either add additional JOINs on CatalogVersion type or you can use a simple sub select. Just hit https://help.hybris.com/6.7.0/hcd/8bc33bb28669101481ccfb446695e9de.html and read through section "Subselects" to get an idea.

[andyfletcher · Accepted solution]

Just add some more joins to catalogversion and catalog types

 select {cat.code} from {product as p
 join catalogversion as cv on {p.catalogversion}={cv.pk}
 join catalog as c on {cv.catalog}={c.pk}
 join categoryproductrelation as cpr on {cpr.target}={p.pk}
 join category as cat on {cpr.source}={cat.pk}}
 where {c.id}='my-catalog' and {cv.version}='Online' and {p.code}='XYZ'

[arvind-kumar_avinash]

You can use the following Flexible Search Query:

 SELECT {p:code}, {c:code} FROM
  {
     Product as p JOIN CategoryProductRelation as rel
     ON {p:PK} = {rel:target}
     JOIN Category AS c
     ON {rel:source} = {c:PK}
  } 
  WHERE {p:code}='1934794' AND {p:catalogVersion}=({{ SELECT {PK} FROM {catalogVersion} WHERE {catalog}=({{SELECT {PK} FROM {catalog} WHERE {id}='electronicsProductCatalog'}}) AND {version}='Staged' }})

Alternatively, you can even remove the keyword, JOIN altogether as follows:

 SELECT {p:PK}, {c:code}
 FROM {Product as p}, {CategoryProductRelation as rel}, {Category AS c}
 WHERE
     {p:PK} = {rel:target}
     AND {rel:source} = {c:PK}
     AND {p:code}='1934794'
     AND {p:catalogVersion}=({{SELECT {PK}
                             FROM {catalogVersion}
                             WHERE
                                 {catalog}=({{SELECT {PK}
                                             FROM {catalog}
                                             WHERE
                                                 {id}='electronicsProductCatalog'}})
                                                 AND {version}='Staged' }})

Note: Please change the values for code and id as per your requirement. Andrew's query is cleaner than mine but mine is simpler IMHO.

[Former Member]

you need to use CATEGORYPRODUCTRELATION to fetch the required info. here is the solution which should work

select {p.code},{c.code} from {CategoryProductRelation as r on {p.pk}={r.target} join category as c on {r.source}={c.pk}