How to get all BaseStores associated with specific Catalog using FlexibleSearch query

[vvsbc]

Using flexible search query, I wanted to search all base stores associated with a certain catalog provided as an input identified by ID (e.g. UnitedStatesCatalog).

Can someone please recommend such query which I can run on HAC and test the response?

Entities I am looking to join: BaseStore, Catalogs, CatalogForBaseStores

[arvind-kumar_avinash · Accepted solution]

Hi - please use the following query:

 SELECT {bs:uid}, {c:id} FROM
  {
     Catalog as c JOIN CatalogsForBaseStores as rel
     ON {c:PK} = {rel:target}
     JOIN BaseStore AS bs
     ON {rel:source} = {bs:PK}
  } WHERE {c:id}='id-of-the-catalog'

e.g.

 SELECT {bs:uid}, {c:id} FROM
  {
     Catalog as c JOIN CatalogsForBaseStores as rel
     ON {c:PK} = {rel:target}
     JOIN BaseStore AS bs
     ON {rel:source} = {bs:PK}
  } WHERE {c:id}='electronicsProductCatalog'

[vvsbc · Accepted solution]

Thanks

[arvind-kumar_avinash · Accepted solution]

You are most welcome.

[Former Member]

Use below query as it's many to many relation -

SELECT {bs.pk} FROM { BaseStore as bs
JOIN CatalogsForBaseStores as rel ON {rel:target} = {bs.pk}
JOIN Catalog as cat ON {rel:source} = {cat.pk}}

[vvsbc]

Thanks Prashant for your response, your solution did work after I replaced bs.pk with bs:pk and cat.pk with cat:pk