Flex Search: query for an object which does not have any localization - eg getting Scripts with description

[Former Member]

Dear community,

I am trying to query for eg Scripts with their (optional) description:

 select {code} AS code, {description[de]} from {Script}

The problem is that this query only returns me those Scripts which have a description specified.
All Scripts without any description wont get listed.
The background for this behavior is that hybris translates this flexible search query to the following sql query:

 SELECT  item_t0.p_code  AS code,  lp_t0.p_description  
 FROM scripts item_t0 
 JOIN scriptslp lp_t0 ON item_t0.PK = lp_t0.ITEMPK AND lp_t0.LANGPK =?  
 WHERE (item_t0.TypePkString=? )

It is using an INNER JOIN on the localization table scriptslp, thus only querying for those entries which have an entry in the localization table. But if the object got created without specifying any localized values this object wont appear in the result. For the Scripts it is happening for example when you save the Script via the Scripting languages console /hac/console/scripting.

So my question is how can i tell flex search to somehow do an OUTER JOIN on the localization table to fetch all my Scripts?

The same problem applies to not only Scripts but all objects as flex search is using by default an INNER JOIN on the localization table, thus expecting that there is ALWAYS any localization available.

[former_member620692]

You can use 😮 as mentioned in the Syntax Overview section at https://help.hybris.com/1808/hcd/8bc399c186691014b8fce25e96614547.html

[former_member620692]

Hi - I hope it helped you solve your problem.

[Former Member]

yes! thank you very much!! :-)