FlexibleSearch mystic AND WHERE

[Former Member]

Can someone explain me following translatins from FlexibleSearch to SQL?

FlexibleSearch:

 SELECT {C.uid} from {Customer as C} where {C.uid} = 'anonymous'

SQL-Query (invalid):

 SELECT  item_t0.p_uid  FROM users item_t0 WHERE ( item_t0.p_uid  = 'anonymous') AND (item_t0.TypePkString=? )

SQL-Query (valid):

 SELECT  item_t0.p_uid  FROM users item_t0 WHERE ( item_t0.p_uid  = 'anonymous')

Where does following come from and why:

 ... AND (item_t0.TypePkString=? )

[arvind-kumar_avinash · Accepted solution]

Every itemtype in hybris has a TypePkString and when we execute an FS Query, it searches not just the itemtype but also its subtype e.g. in my hybris application, the following FS Query

 SELECT {pk} FROM {Product}

is translated into

 SELECT  item_t0.PK  FROM products item_t0 WHERE (item_t0.TypePkString IN  (?,?,?,?,?,?,?) )

If I go to hAC > Maintenance > Deployment, I can find the TypePkString for each of these ? (one for Product and six for its subtypes) by hovering mouse over the rows with typecode 1.

On the other hand, the following query

 SELECT {pk} FROM {Product!}

gets translated into

 SELECT  item_t0.PK  FROM products item_t0 WHERE (item_t0.TypePkString=? )

as I have restricted it to just Product by using !.

Now, the question is, why it is ? and not the TypePkString value itself, and the answer to this question is JDBC PreparedStatement. Basically, the FS Query gets translated into PreparedStatement and displayed in the SQL Query tab of hAC. When we are on Flexible Query tab and click Execute button, this PreparedStatement is processed by the responsible Java program to fetch the data from the database and display in Search result tab. For any DB/SQL client (including the SQL Query tab in hAC), this PreparedStatement is invalid.

I hope it is clear.

[Former Member · Accepted solution]

Nicely explained!!

[arvind-kumar_avinash · Accepted solution]

Thanks, .