How to write flexible search query on sub type attribute

[Former Member]

I have a requirement something like this Parent Type, Child1 extends Parent, child 2 extends Parent,child3 extends Parent............ Only parenttype has a deployment table with common attributes.

Now I need to run a query to fetch a common attribute in parent with- or seperated where clauses on different subtype attributes

Like,

Select * from {user} where {mobileNumber}='xxxx' (but mobileNumber is defined in customerModel not UserModel) Result is required from query directly without typecasting. Any suggestions will be really grateful.

[andyfletcher]

You could do something like this with a union. The syntax is quite obscure because of the way Hybris parses the flexiblesearch.

 select common_attribute from child1 where child1_attribute = ?
 union
 select common_attribute from child2 where child2_attribute = ?

in a Customer and Employee example becomes

 select uid from
 (
   {{select {uid} as uid from {customer} where {title} is not null}}
   union
   {{select {uid} from {employee} where {sessionCurrency} is not null}}
 ) x

The x is an alias for the derived table, which is required. I'm not entirely sure whether this Hybris or Mysql requiring it.

[former_member570877]

Hi,

You can directly run flexible search on subtypes:

String query = Select {pk} from {Customer} where {mobileNumber} = 'xxx' FlexibleSearhQuery fquery = new FlexibleSearchQuery(query) final SearchResult result = getFlexibleSearchService().search(fquery);

Please note that query works on Types not on Models.

Similarly, if you want to fetch only parent type i.e. User in the example, you should write:

String query = Select {pk} from {User!} where {uid} = 'xxx'

Thanks,
Vikram

[VinayKumarS]

you write a join between parent and child table. ..

[Former Member]

 String query =  Select {pk} from {CustomerModel} where {mobileNumber} = 'xxx'
 FlexibleSearhQuery fquery = new FlexibleSearchQuery(query)
 
 final SearchResult<CustomerModel> result = getFlexibleSearchService().search(fquery);
 

Basically if C extends B and B extends A, querying on C will give you attributes of both B & A if only A has a deployment table.

No typecasting required.