DBMS Agnostic way to limit results count in inner Flexible Search

[former_member1154393]

Is there a way to limit the result count in an inner query in a DBMS agnostic way? On the outer query I can use the de.hybris.platform.servicelayer.search.AbstractQuery.setCount(int) but I do not know how to do it on the inner query. By example this will work only on MySQL:

 SELECT *
        FROM ${type}
        WHERE
     
        {{
           SELECT *
              FROM ${other_type}
              WHERE ${subselect_search_condition}
              LIMIT 5
        }}
 
 

[Former Member]

There is something like paging you can use here. However there is already a similar thread regarding your topic. Below find the snippet regarding the paging (took from the wiki page), maybe you can apply this to your use-case:

 int start = 0;
 final int range = 3;
 int total;
  
 String query = "SELECT {" + UnitModel.PK + "} FROM {"+ UnitModel._TYPECODE + "} ORDER BY " + UnitModel._TYPECODE;
 final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(query);
 fQuery.setCount(range);
 fQuery.setNeedTotal(true);
  
 do
 {
   fQuery.setStart(start);
   final SearchResult<LanguageModel> searchResult = flexibleSearchService.search(fQuery);
   total = searchResult.getTotalCount();
   start += range;
 }
 while(start < total);