Class FlexibleSearch

java.lang.Object
de.hybris.platform.jalo.Manager
de.hybris.platform.jalo.flexiblesearch.FlexibleSearch
All Implemented Interfaces:
ItemLifecycleListener, Serializable
Direct Known Subclasses:
FlexibleSearchDataSourceTest.TestFlexibleSearch, FlexibleSearchHintsTest.TestFlexibleSearch, ReadOnlyFlexibleSearchCronJobTest.TestFlexibleSearch

@Deprecated(since="ages", forRemoval=false) public class FlexibleSearch extends Manager
Deprecated.
since ages - useFlexibleSearchService and SearchRestrictionService instead.
The FlexibleSearch is an SQL-based search on item types which allows to search on attribute values as well as directly on database columns. Unlike SQL statements, a FlexibleSearch query can contain references to items in the hybris Platform (products, units, customers, and so on) as parts of the query statement (marked by { and }). Therefore, a FlexibleSearch query is first interpreted (resolving the references) and then executed. During the interpretation phase, the FlexibleSearch framework resolves those references and replaces them with the actual names of columns and tables. This phase also does automatic joins of property tables where necessary.

An overview on the FlexibleSearch syntax:

 <query> = "SELECT" <selects> "FROM" <types> ( "WHERE" <conditions> ( "ORDER BY" <order> )? )?
 <selects> = SQL <i>select</i> expression that contains attribute <field> sections
 <types> = SQL <i>from</i> expression that contains item <type> and <typejoin> sections
 <type> = "{" <single-type-clause> "}"
 <typejoin> = "{" <single-type-clause> ( ( "LEFT" )? "JOIN" <single-type-clause> ( "ON" <join-conditions> )? )+ "}"
 <single-type-clause> = code of type "!"? ( "AS" <type-alias> )? <type-modifiers>?
 <type-alias> = a unique name of this type inside this query
 <join-conditions> = SQL boolean condition including <field> sections ( "AND" SQL boolean condition including <field> sections )*
 <conditions> = SQL <i>where</i> expression containing attribute <field> sections and <subselect> sections
 <order> = SQL <i>order by </i> expression containing attribute <field> sections
 <field> = "{" ( <type-alias> ":" )? <attribute-name> <language>( ":" <modifiers> )? "}"
 <attribute-name> = the attribute name
 <language> = "[" language PK string + "]"
 <modifiers> = ( "c" | "l")? "o"? ... core field , localized property, outer joined property
 <subselect> = "{{" <query> "}}"
 

A simple select to find all items of one type can be done like this:

 ComposedType unitType = jaloSession.getTypeManager().getComposedType(Unit.class);
 String query = "SELECT {" + Item.PK + "} FROM {" + unitType.getCode() + "}";
 List units = jaloSession.getFlexibleSearch().search(query, // the query text
                Collections.EMPTY_MAP, // no values needed here
                Collections.singletonList(Unit.class), // the expected result (row) signature
                true, // fail on unknown (untyped) fields
                true, // don't need total
                0, -1 // result range start = 0, count = -1 (unlimited)
                ).getResult();
 

Please note that items are always referenced as "{" + Item.PK + "}" . The actual item type is defined by the search result signature list, e.g. Collections.singletonList(Item.class).

If you know that a search query is likely to yield a lot of results (such as a search for all products and their variants, for example), we discourage getting all of those search results in a single FlexibleSearch call: the results will be stored in a single Collection, which may result in slow performance. A better way is to split the query into several calls by limiting the number of search results per call, as in the following code snippet:

 ComposedType unitType = jaloSession.getTypeManager().getComposedType(Unit.class);
 String query = "SELECT {" + Item.PK + "} FROM {" + unitType.getCode() + "}";
 int start = 0;
 final int range = 3;
 int total = -1;
 do
 {
        SearchResult res = jaloSession.getFlexibleSearch().search(query, // the query text
                        Collections.EMPTY_MAP, // no values needed here
                        Collections.singletonList(Unit.class), // the expected result (row) signature
                        true, // fail on unknown (untyped) fields
                        false, // disable 'don't need total' here !
                        start, range // result range start = 0, count = -1 (unlimited)
                        );
        List unitRange = res.getResult();
        //
        // process result
        //
        total = res.getTotalCount();
        start += range;
 }
 while (start < total);
 

If you run such an iterated statement, you need to set the dontNeedTotal parameter to false.
If it is set to true, the FlexibleSearch framework runs the query, retrieves a list of Primary Keys, and instantiates the items referenced by those keys -- which results in a possibly large number of additional database queries.
If dontNeedTotal is set to false, however, the FlexibleSearch does not instantiate the items referenced by the Primary Keys; instead, it runs an SQL count statement on the list of Primary Keys. That way, you will be able of quickly getting the number of search results; but you won't be able of accessing the actual items as they are not instantiated.

Unlike EJB finders, the FlexibleSearch is able of retrieving raw data as well as items. Furthermore, it is possible to select multiple attributes or columns. the result of multi-attribute selections is a list of lists.

 ComposedType unitType = jaloSession.getTypeManager().getComposedType(Unit.class);
 String query =
 // select
 "SELECT {" + Item.PK + "} " + // the unit itself
                ",LOWER({" + Unit.CODE + "})" + // get the code cmp field
                ",{" + Unit.NAME + "}" + // get the localized name
                // from
                "FROM {" + unitType.getCode() + "}";
 List rows = jaloSession.getFlexibleSearch().search(query, // the query text
                Collections.EMPTY_MAP, // no values needed here
                Arrays.asList(new Class[]
        { Unit.class, String.class, String.class }), // the expected result row signature: Unit, String, String
                true, // fail on unknown (untyped) fields
                true, // don't need total
                0, -1 // result range start = 0, count = -1 (unlimited)
                ).getResult();
 for (int i = 0, s = rows.size(); i < s; i++)
 {
        List row = (List) rows.get(i);
        Unit u = row.get(0);
        String codeLowerCase = row.get(1);
        String name = row.get(2);
        // ...
 }
 

As seen here it is possible to use any column based SQL functions around flexible search sections ( "LOWER({"+Unit.CODE+"}" ) since only these sections are replaced. the rest of the query is left untouched. this way even database specific functionality is available (though this might break database independence).

ordering is as simple as selecting different attributes

 ComposedType unitType = jaloSession.getTypeManager().getComposedType(Unit.class);
 String query = "SELECT {" + Item.PK + "} FROM {" + unitType.getCode() + "}" + "ORDER BY {" + Unit.CODE + "} ASC";
 List unitsOrdered = jaloSession.getFlexibleSearch().search(query.toString, // the query text
                Collections.EMPTY_MAP, // no values needed here
                Collections.singletonList(Unit.class), // the expected result (row) signature
                true, // fail on unknown (untyped) fields
                true, // don't need total
                0, -1 // result range start = 0, count = -1 (unlimited)
                ).getResult();
 

a special note on properties: when order by contains properties which are stored in separate tables these tables are outer joined automatically since non existence of the property would avoid the actual item to be found otherwise.

localized properties require that a language is specified for searching. normally search(SessionContext, String, Map, List, boolean, boolean, int, int) uses the current session language.

        SessionContext myLangCtx = jaloSession.createSessionContext();
        myLangCtx.setLanguage( jaloSession.getC2LManager().getLanguage( "FR" ) );
        jaloSession.getFlexibleSearch().search( myLangCtx, ... );
 

further it is possible to specify a fixed language for each localized attribute - even multiple different languages per query. please note that this generates as many joins to localized property tables as languages are specified, so be careful.

 Language langDE = jaloSession.getC2LManager().getLanguiage("DE");
 Language langEN = jaloSession.getC2LManager().getLanguiage("EN");
 String query = "SELECT {PK} FROM {MyType} WHERE {locAttr[" + langDE.getPK() + "]} = 'x' AND " + "{locAttr2[" + langEN.getPK()
                + "]} = 'y' AND " + "{locAttr3} = 'z'";
 

the example uses infact 3 languages: the session language for locAttr3, langDE for locAttr and langEN for locAttr2. the fixed languages are passed as language pk strings.

values should be passed similar to JDBC prepared statements. unlike JDBC flexible search allows named values - this way the same value may be referenced at multiple places without the need to build a value list.

        String query = "SELECT {PK} FROM {MyType} WHERE {attr} = ?value OR {attr2} = ?value";
        Map values = new HashMap();
        values.put( "value", "xyz" );
        jaloSession.getFlexibleSearch().search( query, values, ... );
 

besides explicitly defined values a query may reference all session context attributes too. these attributes are named "session."+attribute name. predefined session attributes are SessionContext.USER, SessionContext.LANGUAGE and SessionContext.CURRENCY.

        ComposedType orderType = jaloSession.getTypeManager().getComposedType( Order.class );
        String query = "SELECT {"Item.PK+"} FROM {"+orderType.getCode()+"} WHERE {"+Order.USER+"} = ?session."+SessionContext.USER +
                                                " AND {"+Order.CURRENCY+"} = ?session."+SessionContext.CURRENCY;
        List userOrders = jaloSession.getFlexibleSearch().search(
                query,
                Collections.EMPTY_MAP,  // no values required since we use a session attribute
                Collections.singletonList( Order.class ),
                true,                                                   // fail on unknown
                true,                                                   // don't need total
                0, -1                                                   // get all
        ).getResult();
 

further it is possible to access even attributes of the actual (item) values inside the value map.

 Map values = new HashMap();
 values.put("country", jaloSession.getC2LManager().getCountry("DE"));
 values.put("region", jaloSession.getC2LManager().getRegionByCode("sachsen"));
 String query = "SELECT {" + Item.PK + "} FROM {" + jaloSession.getTypeManager().getComposedType(Language.class).getCode()
                + "} AS lang " + "WHERE {lang:" + Language.ISOCODE + "} IN( ?country." + Country.ISOCODE + ", ?region." + Region.COUNTRY
                + "." + Country.ISOCODE + " )";
 

generally stating from one item each readable attribute is usable - if it is an item too further traversal is possible.

joins between types are allowed. currently inner joins and left outer joins are allowed.

        String productType = jaloSession.getTypeManager().getComposedType( Product.class ).getCode();
        STring cartEntryType = jaloSession.getTypeManager().getComposedType( CartEntry.class ).getCode();
        String query =
                "SELECT {p:"Item.PK+"}, {e:"CartEntry.UNIT+"}, SUM( {e:"CartEntry.QUANTITY+"} ) AS amount "+
                "FROM {"+productType+" AS p JOIN "+cartEntryType+" AS e ON {p:"+Item.PK+"} = {e:"+CartEntry.PRODUCT+"} "+
                "ORDER BY amount DESC "+
                "GROUP BY {p:"+Item.PK+"}, {e:"CartEntry.UNIT+"};
        final List productCartLists = jaloSession.getFlexibleSearch().search(
                query,
                Collections.EMPTY_MAP,
                Arrays.asList( new Class[]{Product.class,Unit.class,Long.class}),
                true,
                true,
                0, -1
        ).getResult();
        System.out.println("products currently in carts");
        for( int i = 0, s = productCartLists.size(); i < s ; i++ )
    {
                List row = (List)productCartLists.get(i);
                Product p = (Product)row.get(0);
                Unit u = (Unit)row.get(1);
                long quantity = ((Long)row.get(2)).longValue();
                System.out.println("\t"+p.getCode()+" -> "+quantity+" "+u.getCode());
    }
 

the above example produces a list of all products which are currently held in carts grouped by the ordered unit and show the total quantity. since this is no left (outer) join all products which are not inside a cart don't show up in the result.

using properties requires some additional thought when searching for absence of values or properties not having a certain value.
background: since localized and unoptimized properties are stored in separate tables they have to be joined to be included them in the query. a non-existing property row (e.g. when no value has been written for the current language yet) will cause the item not be found (except any property used in order by , see above )
an example:

        Language l = jaloSession.getC2LManager().createLanguage( "fresh" );
        SessionContext myCtx = jaloSession.createSessionContext();
        myCtx.setLanguage( l );
        List list = jaloSession.getFlexibleSearch().search(
                myCtx,
                "SELECT {"+Item.PK+"} FROM {"jaloSession.getTypeManager().getComposedType(Product.class).getCode()+"}"+
                        "WHERE {"+Product.NAME+"} <> 'tom'";
                Collections.EMPTY_MAP,
                Collections.singletonList( Product.class ),
                true,
                true,
                0, 1
        ).getResult();
        // list should be empty now since no product can own a property for the newly created language
 

the solution here is the o attribute modifier. this forces the property table to be outer joined. nevertheless the query has to be changed as well to achieve the desired result here:

<pre Language l = jaloSession.getC2LManager().createLanguage( "fresh" ); SessionContext myCtx = jaloSession.createSessionContext(); myCtx.setLanguage( l ); List list = jaloSession.getFlexibleSearch().search( myCtx, // modifiers require full qualified attributes so we need an alias "SELECT {"+Item.PK+"} FROM {"jaloSession.getTypeManager().getComposedType(Product.class).getCode()+" AS p }"+ "WHERE ( {p:"+Product.NAME+":o} <> 'tom' OR {p:"+Product.NAME+":o} IS NULL )"; Collections.EMPTY_MAP, Collections.singletonList( Product.class ), true, true, 0, 1 ).getResult(); // now we should have all products inside the list

as seen here a IS NULL check has to be added since even though the property row is outer joined column <> 'xyz' will fail since the column is in fact NULL.
generally it is advised to avoid such queries whenever possible. trying to set default values is always a far better solution than searching for absence ( IS NULL ) in properties.

there is a special syntax for subselects too. please note that subselects are not available in case the underlying database doesn't support them!

 String productType = jaloSession.getTypeManager().getComoposedType(Product.class).getCode();
 String orderEntryType = jaloSession.getTypeManager().getComoposedType(OrderEntry.class).getCode();
 String query = "SELECT {" + Item.PK + "} FROM {" + productType + " AS p} WHERE " + " EXISTS ( {{" + "SELECT {" + Item.PK
 + "} FROM {" + orderEntryType + "} WHERE {" + OrderEntry.PRODUCT + "} = {p:" + Item.PK + "} " +
 // be sure to have a space between the last '}' and '}}' - this is a know issue right now
 "}} )";
 

subselects may contain all features as normal queries including joins and nested subselects.

the result of a flexible query is always limited to the queried type, which means result items are instances of the queried type or any of its subtypes. it is possible to select instances of one type exactly by appending a ! to the type code.

 String query = "SELECT {PK} FROM {MyType!}";
 
See Also: