CODEWITHSUNDEEP
sqlsap-commerce-cloudflexible-search
User2413
User2413

Reputation: 615

Hybris Flexible search union query to fetch products

   code    Attribute1(String)

    A         C
    B         D
    C         Empty
    D         Empty

how to get the pk's of all A,B,C,D

Note: Using the string value C,D I want to fetch pk of product C,D along with A,B using Flexible search query

Details :

I Have list a of product's.

Inside each of these products there is an attribute called "X" which contains product ID code of type string.

Note : "Product ID Code" means "Product ID" of another product inside the list of products.

Now i want to get pk's of products based upon the Product ID Codes?

Upvotes: 1

Views: 3395

Answers (1)

HybrisHelp
HybrisHelp

Reputation: 5810

I don't understand it fully, but you can try something like this

select {p1.pk},{p2.pk} from {product as p1},{product as p2} WHERE {p1.Attribute1} = {p2.code}

you can add filter to it

 AND {p1.Attribute1} in ('C','D')

Using UNION

SELECT uniontable.PK FROM
(
   {{
      SELECT {p1:PK} AS PK FROM {Product AS p1},{Product AS p2} 
      WHERE {p1.code} = {p2.Attribute1}
   }}
   UNION ALL
   {{
      SELECT {p:PK} AS PK FROM {Product AS p}
      WHERE {p1.Attribute1} is not empty
   }}
) uniontable

With filter

SELECT uniontable.PK FROM
(
   {{
      SELECT {p1:PK} AS PK FROM {Product AS p1},{Product AS p2} 
      WHERE {p1.code} = {p2.Attribute1} AND {p2.Attribute1} in ('C','D')
   }}
   UNION ALL
   {{
      SELECT {p:PK} AS PK FROM {Product AS p}
      WHERE {p.Attribute1} in ('C','D')
   }}
) uniontable

Upvotes: 0

Related Questions