title: How does Flexible Search query translate to SQL query in Hybris?
body: <p>As far as I know, Hybris uses FlexibleSearchQuery to query data. So to query the database, I need to translate FlexibleSearchQuery into SQL. I still do not know how Hybris does it.Can you help me understand?</p>
  
  <p>I have stack trace code as follows:</p>
  
  <p>I see: TranslatedQuery tQuery(class: FlexibleSearch method: executeSearch) = this.cacheKey.tq (class: FlexibleSearchCacheUnit method: compute).</p>
  
  <p>So, Where does this.cacheKey.tq value come from?</p>
  
  <pre><code>PreparedStatementImpl   executeQuery
  
  public ResultSet executeQuery() throws SQLException {
          if (this.isLoggingActivated()) {
              long startTime = System.currentTimeMillis();
  
              ResultSet var5;
              try {
                  var5 = this.wrapResultSet(this.prepStmtPassthru.executeQuery());
              } catch (SQLException var9) {
                  this.getConnection().notifyError(var9);
                  this.getConnection().logError(var9, "error executing query");
                  throw var9;
              } finally {
                  this.connection.getDataSource().getLogUtils().logElapsed(Thread.currentThread().getId(), this.connection.getConnectionID(), startTime, "statement", this.preparedQueryWithoutStackTrace, this.assembleQueryFromPreparedStatement());
              }
  
              return var5;
          } else {
              try {
                  return this.wrapResultSet(this.prepStmtPassthru.executeQuery());
              } catch (SQLException var11) {
                  this.getConnection().notifyError(var11);
                  this.getConnection().logError(var11, "error executing query");
                  throw var11;
              }
          }
      }
  
  FlexibleSearchExecutor  execute
  
  public SearchResult execute(int start, int count, boolean dontNeedTotal, TranslatedQuery translatedQuery, List&lt;Class&lt;?&gt;&gt; resultClasses, Map values, PK languagePK, int prefetchSize, Set&lt;PK&gt; prefetchLanguages) {
          ConversionMetaInformation conversionResult = this.convertResultClasses(resultClasses);
          ExecutableQuery executableQuery = this.getExecutableQuery(translatedQuery, values, languagePK);
          JDBCValueMappings jdbcVM = JDBCValueMappings.getInstance();
          LimitStatementBuilder limitBuilder = this.limitStatementFactory.getLimitStatementBuilder(executableQuery, start, count);
          Connection connection = null;
          PreparedStatement statement = null;
          ResultSet resultSet = null;
          String query = null;
  
          SearchResult result;
          try {
              String errorMessageSecondPart;
              try {
                  connection = this.tenant.getDataSource().getConnection();
                  if (this.isNeedTotalAndCountIsZero(count, dontNeedTotal)) {
                      int totalCount = jdbcVM.getTotalCountFromCountQuery(connection, executableQuery.getCountSQL(), executableQuery.getCountValueList());
                      result = SQLSearchResultFactory.createCountOnlyResult(totalCount, start, count, executableQuery.getSQL(), executableQuery.getValueList());
                  } else {
                      query = limitBuilder.getModifiedStatement();
                      statement = connection.prepareStatement(query);
                      jdbcVM.fillStatement(statement, limitBuilder.getModifiedStatementValues());
                      long t1 = 0L;
                      long t2 = 0L;
                      long t3 = 0L;
                      long t4 = 0L;
                      if (LOG.isDebugEnabled()) {
                          t1 = System.currentTimeMillis();
                      }
  
                      resultSet = statement.executeQuery();
                      if (LOG.isDebugEnabled()) {
                          t2 = System.currentTimeMillis();
                      }
  
                      int _start = limitBuilder.getOriginalStart();
                      int _count = limitBuilder.getOriginalCount();
                      if (limitBuilder.hasDbEngineLimitSupport()) {
                          _start = 0;
                          _count = -1;
                      }
  
                      if (LOG.isDebugEnabled()) {
                          t3 = System.currentTimeMillis();
                      }
  
                      RowFetchResult rowFetchResult = jdbcVM.getQueryResults(resultSet, conversionResult.getConvertedClasses(), _start, _count);
                      if (LOG.isDebugEnabled()) {
                          t4 = System.currentTimeMillis();
                      }
  
                      int totalCount = dontNeedTotal ? rowFetchResult.rows.size() : jdbcVM.getTotalCount(resultSet, rowFetchResult, start, count, connection, executableQuery, limitBuilder.hasDbEngineLimitSupport());
                      if (LOG.isDebugEnabled()) {
                          LOG.debug(this.getInfoForLogging(executableQuery.getSQL(), limitBuilder.getModifiedStatement(), conversionResult.getConvertedClasses(), executableQuery.getValueList(), ", original range=[" + limitBuilder.getOriginalStart() + "," + limitBuilder.getOriginalCount() + "], rowcount=" + totalCount + ", query=" + (t2 - t1) / 1000L + "ms, fetch=" + (t4 - t3) / 1000L + "ms"));
                      }
  
                      result = SQLSearchResultFactory.createCacheable(new CacheableResultHolder(rowFetchResult, prefetchSize, prefetchLanguages, conversionResult), totalCount, start, count, limitBuilder.getModifiedStatement(), executableQuery.getValueList());
                  }
              } catch (IllegalArgumentException var35) {
                  errorMessageSecondPart = Config.getBoolean("flexible.search.exception.show.query.details", false) ? var35.getMessage() + " query = '" + executableQuery.getSQL() + "', values = " + executableQuery.getValueList() : "enable the property 'flexible.search.exception.show.query.details' for more details";
                  throw new FlexibleSearchException(var35, "wrong flexible search parameter - " + errorMessageSecondPart, 0);
              } catch (SQLException var36) {
                  errorMessageSecondPart = Config.getBoolean("flexible.search.exception.show.query.details", false) ? var36.getMessage() + " query = '" + query + "', values = " + executableQuery.getValueList() : "enable the property 'flexible.search.exception.show.query.details' for more details";
                  throw new FlexibleSearchException(var36, "SQL search error - " + errorMessageSecondPart, 0);
              }
          } finally {
              Utilities.tryToCloseJDBC(connection, statement, resultSet);
          }
  
          return result;
      }
  
  FlexibleSearch  executeSearch
  
  protected SearchResult executeSearch(TranslatedQuery tQuery, Map values, PK langPK, List&lt;Class&lt;?&gt;&gt; resultClasses, boolean dontNeedTotal, int start, int count, int prefetchSize, Set&lt;PK&gt; prefetchLanguages, boolean doExecuteQuery) throws FlexibleSearchException {
          return doExecuteQuery ? this.fsExecutor.execute(start, count, dontNeedTotal, tQuery, resultClasses, values, langPK, prefetchSize, prefetchLanguages) : this.fsExecutor.simulate(start, count, tQuery, values, langPK);
      }
  
  FlexibleSearchCacheUnit compute
  
  public final Object compute() throws FlexibleSearchException {
          this.usedCachedResult = false;
          StandardSearchResult ret = (StandardSearchResult)this.fs.executeSearch(this.cacheKey.tq, this.values, this.cacheKey.langPK, Arrays.asList(this.cacheKey.resultClasses), this.cacheKey.hasModifier(1), this.cacheKey.start, this.cacheKey.count, this.prefetchSize, this.prefetchLanguages, this.cacheKey.hasModifier(2));
          ret.setUnitHash(System.identityHashCode(this));
          return ret;
      }
  
  AbstractCacheUnit   privateGetNoLock
  
  private final Object privateGetNoLock() throws Exception {
          Object ret = this.value;
          if (this.isValueUnknown(ret)) {
              ret = this.compute();
              this.value = ret;
          }
  
          return ret;
      }
  
  AbstractCacheUnit   privateGet
  
  private final Object privateGet() throws Exception {
          return this.getTransactionAwareCache().isForceExclusiveComputation(this) ? this.privateGetSynchronized() : this.privateGetNoLock();
      }
  
  AbstractCacheUnit   get
  
  public final Object get() throws Exception {
          if (!this.useCache()) {
              return this.computeUncached();
          } else {
              TransactionAwareCache transactionAwareCache = this.getTransactionAwareCache();
              boolean success = false;
              AbstractCacheUnit valueUnit = transactionAwareCache.getOrAddUnit(this);
              Object ret = valueUnit.value;
  
              try {
                  ret = this.isValueUnknown(ret) ? valueUnit.privateGet() : ret;
                  success = true;
              } catch (CacheValueLoadException var9) {
                  if (var9.getCause() instanceof Exception) {
                      throw (Exception)var9.getCause();
                  }
  
                  throw var9;
              } finally {
                  if (!success) {
                      transactionAwareCache.removeUnit(valueUnit);
                  }
  
              }
  
              return ret;
          }
      }
  
  FlexibleSearchCacheUnit myGet
  
  public final StandardSearchResult myGet() throws Exception {
          StandardSearchResult ret = (StandardSearchResult)this.get();
          ret.setFromCache(this.usedCachedResult);
          return ret;
      }
  
  FlexibleSearch  search
  
  public SearchResult search(SessionContext _ctx, String query, Map values, List resultClasses, boolean failOnUnknownFields, boolean dontNeedTotal, int start, int count) throws FlexibleSearchException {
          TranslatedQuery tQuery = null;
          int prefetchSize = this.getSearchPrefetchSize(_ctx);
          if (LOG.isDebugEnabled()) {
              LOG.debug(this.getSearchDebug(_ctx, query, resultClasses, failOnUnknownFields, dontNeedTotal, start, count, prefetchSize));
          }
  
          boolean doExecuteQuery = !this.disableExecution(_ctx);
          SessionContext localCtx = null;
  
          SearchResult var22;
          try {
              if (_ctx != null) {
                  localCtx = JaloSession.getCurrentSession(this.getTenant()).createLocalSessionContext(_ctx);
                  localCtx.setAttribute("disableExecution", Boolean.FALSE);
              }
  
              PK langPK = this.getSearchLangPK(localCtx);
              tQuery = this.translate(localCtx, this.getRestrictionPrincipal(localCtx), query, values != null ? values.size() : 0, langPK != null, failOnUnknownFields, this.disableRestrictions(localCtx), this.disablePrincipalGroupRestrictions(localCtx), values);
              Map _values = tQuery.removeUnusedValues(this.translatePathValueKeys(localCtx, tQuery.getValueKeys(), new CaseInsensitiveParameterMap(values != null ? values : Collections.EMPTY_MAP)));
              Set&lt;PK&gt; queryTCs = tQuery.getTypePKs();
              Set&lt;Integer&gt; beanTCs = new HashSet(queryTCs.size() * 4);
              PersistenceManager pm = this.getTenant().getPersistenceManager();
              Iterator var19 = queryTCs.iterator();
  
              while(var19.hasNext()) {
                  PK typePK = (PK)var19.next();
                  beanTCs.addAll(pm.getBeanTypeCodes(typePK));
              }
  
              if (beanTCs.isEmpty()) {
                  throw new JaloInvalidParameterException("empty bean typcode list ( typePKs = " + tQuery.getTypePKs() + ", query = " + tQuery.getSQLTemplate() + " )", 0);
              }
  
              try {
                  if (!this.isCachingDisabled(localCtx)) {
                      int ttl = this.getTTL(localCtx);
                      FlexibleSearch.FlexibleSearchCacheKey cacheKey = this.createCacheKey(resultClasses, dontNeedTotal, start, count, tQuery, doExecuteQuery, langPK, _values, beanTCs, ttl);
                      FlexibleSearchCacheUnit cacheUnit = this.createCacheUnit(prefetchSize, WrapperFactory.getPrefetchLanguages(localCtx), _values, cacheKey);
                      var22 = this.wrapSearchResult(cacheUnit.myGet());
                      return var22;
                  }
  
                  var22 = this.wrapSearchResult(this.executeSearch(tQuery, _values, langPK, resultClasses, dontNeedTotal, start, count, prefetchSize, WrapperFactory.getPrefetchLanguages(localCtx), doExecuteQuery));
              } catch (FlexibleSearchException var28) {
                  throw var28;
              } catch (Exception var29) {
                  throw new FlexibleSearchException(var29, var29.getMessage(), 0);
              }
          } catch (FlexibleSearchException var30) {
              LOG.error("Flexible search error occured...");
              if (tQuery == null) {
                  LOG.error("Query translation was not successful.");
              }
  
              throw var30;
          } catch (JaloItemNotFoundException var31) {
              throw new JaloInternalException(var31, "cannot get type by translated query type pk", 0);
          } finally {
              if (localCtx != null) {
                  JaloSession.getCurrentSession(this.getTenant()).removeLocalSessionContext();
              }
  
          }
  
          return var22;
      }
  
  FlexibleSearch  search
  
  public SearchResult search(String query, Map values, List resultClasses, boolean failOnUnknownFields, boolean dontNeedTotal, int start, int count) throws FlexibleSearchException {
          return this.search(JaloSession.hasCurrentSession() ? this.getSession().getSessionContext() : null, query, values, resultClasses, failOnUnknownFields, dontNeedTotal, start, count);
      }
  
  DefaultFlexibleSearchService$2  execute
  
      private &lt;T&gt; de.hybris.platform.jalo.SearchResult&lt;T&gt; getJaloResult(final FlexibleSearchQuery query) {
          return (de.hybris.platform.jalo.SearchResult)this.getSessionService().executeInLocalView(new SessionExecutionBody() {
              public de.hybris.platform.jalo.SearchResult&lt;T&gt; execute() {
                  DefaultFlexibleSearchService.this.getQueryPreprocessorRegistry().executeAllPreprocessors(query);
                  Map queryParams = (Map)DefaultFlexibleSearchService.this.toPersistenceLayer(query.getQueryParameters());
  
                  try {
                      return FlexibleSearch.getInstance().search(query.getQuery(), queryParams, this.convertModelClassList(query), query.isFailOnUnknownFields(), !query.isNeedTotal(), query.getStart(), query.getCount());
                  } catch (FlexibleSearchException var3) {
                      throw new de.hybris.platform.servicelayer.search.exceptions.FlexibleSearchException(var3.getMessage(), var3);
                  }
              }
  
              private List&lt;Class&gt; convertModelClassList(FlexibleSearchQuery queryx) {
                  List&lt;Class&gt; resultClassList = new ArrayList(queryx.getResultClassList().size());
                  Iterator var4 = queryx.getResultClassList().iterator();
  
                  while(var4.hasNext()) {
                      Class modelClass = (Class)var4.next();
                      resultClassList.add(DefaultFlexibleSearchService.this.getModelService().getModelTypeClass(modelClass));
                  }
  
                  return resultClassList;
              }
          });
      }
  
  DefaultFlexibleSearchService$2  execute
  
  
  
  DefaultSessionService   executeInLocalView
  
  public Object executeInLocalView(SessionExecutionBody body) {
          Object var3;
          try {
              this.getOrCreateCurrentJaloSession().createLocalSessionContext();
              var3 = body.execute();
          } finally {
              this.getOrCreateCurrentJaloSession().removeLocalSessionContext();
          }
  
          return var3;
      }
  
  DefaultFlexibleSearchService    getJaloResult
  
  private &lt;T&gt; de.hybris.platform.jalo.SearchResult&lt;T&gt; getJaloResult(final FlexibleSearchQuery query) {
          return (de.hybris.platform.jalo.SearchResult)this.getSessionService().executeInLocalView(new SessionExecutionBody() {
              public de.hybris.platform.jalo.SearchResult&lt;T&gt; execute() {
                  DefaultFlexibleSearchService.this.getQueryPreprocessorRegistry().executeAllPreprocessors(query);
                  Map queryParams = (Map)DefaultFlexibleSearchService.this.toPersistenceLayer(query.getQueryParameters());
  
                  try {
                      return FlexibleSearch.getInstance().search(query.getQuery(), queryParams, this.convertModelClassList(query), query.isFailOnUnknownFields(), !query.isNeedTotal(), query.getStart(), query.getCount());
                  } catch (FlexibleSearchException var3) {
                      throw new de.hybris.platform.servicelayer.search.exceptions.FlexibleSearchException(var3.getMessage(), var3);
                  }
              }
  
              private List&lt;Class&gt; convertModelClassList(FlexibleSearchQuery queryx) {
                  List&lt;Class&gt; resultClassList = new ArrayList(queryx.getResultClassList().size());
                  Iterator var4 = queryx.getResultClassList().iterator();
  
                  while(var4.hasNext()) {
                      Class modelClass = (Class)var4.next();
                      resultClassList.add(DefaultFlexibleSearchService.this.getModelService().getModelTypeClass(modelClass));
                  }
  
                  return resultClassList;
              }
          });
      }
  
  DefaultFlexibleSearchService    search
  
  public &lt;T&gt; SearchResult&lt;T&gt; search(FlexibleSearchQuery query) {
          ServicesUtil.validateParameterNotNull(query, "The 'query' is null!");
          de.hybris.platform.jalo.SearchResult&lt;T&gt; jaloResult = this.getJaloResult(query);
          if (jaloResult.getCount() == 0) {
              return this.createEmptyResult(jaloResult);
          } else {
              return jaloResult instanceof StandardSearchResult ? this.wrapOrConvert(query, (StandardSearchResult)jaloResult, () -&gt; {
                  return this.convert(jaloResult);
              }) : this.convert(jaloResult);
          }
      }
  
  DefaultPagedFlexibleSearchService   search
  
  public &lt;T&gt; SearchPageData&lt;T&gt; search(final String query, final Map&lt;String, ?&gt; queryParams, final PageableData pageableData)
      {
          validateParameterNotNull(query, "query cannot be null");
          validateParameterNotNull(pageableData, "pageableData cannot be null");
          Assert.isTrue(pageableData.getCurrentPage() &gt;= 0, "pageableData current page must be zero or greater");
          Assert.isTrue(pageableData.getPageSize() &gt; 0, "pageableData page size must be greater than zero");
  
          final FlexibleSearchQuery searchQuery = new FlexibleSearchQuery(query);
          if (queryParams != null &amp;&amp; !queryParams.isEmpty())
          {
              searchQuery.addQueryParameters(queryParams);
          }
  
          searchQuery.setNeedTotal(true);
          searchQuery.setStart(pageableData.getCurrentPage() * pageableData.getPageSize());
          searchQuery.setCount(pageableData.getPageSize());
  
          final SearchResult&lt;T&gt; searchResult = getFlexibleSearchService().search(searchQuery);
  
          // Create the paged search result
          final SearchPageData&lt;T&gt; result = createSearchPageData();
          result.setResults(searchResult.getResult());
          result.setPagination(createPagination(pageableData, searchResult));
          // Note: does not set sorts
  
          return result;
      }
  
  DefaultPagedFlexibleSearchService   search
  
  public &lt;T&gt; SearchPageData&lt;T&gt; search(final List&lt;SortQueryData&gt; sortQueries, final String defaultSortCode,
              final Map&lt;String, ?&gt; queryParams, final PageableData pageableData)
      {
          validateParameterNotNull(sortQueries, "sortQueries cannot be null");
          validateParameterNotNull(defaultSortCode, "defaultSortCode cannot be null");
          validateParameterNotNull(pageableData, "pageableData cannot be null");
          Assert.isTrue(!sortQueries.isEmpty(), "sortQueries must not be empty");
          Assert.isTrue(pageableData.getCurrentPage() &gt;= 0, "pageableData current page must be zero or greater");
          Assert.isTrue(pageableData.getPageSize() &gt; 0, "pageableData page size must be greater than zero");
  
          // Work out which sort and query to use
          final SortQueryData selectedSortQuery = findSortQueryData(sortQueries, pageableData.getSort(), defaultSortCode);
  
          // Execute the query
          final SearchPageData&lt;T&gt; searchPageData = search(selectedSortQuery.getQuery(), queryParams, pageableData);
  
          // Specify which sort was used
          searchPageData.getPagination().setSort(selectedSortQuery.getSortCode());
          searchPageData.setSorts(createSorts(sortQueries, selectedSortQuery.getSortCode()));
          return searchPageData;
      }
  
  DefaultCustomerAccountDao   findOrdersByCustomerAndStore
  
  public SearchPageData&lt;OrderModel&gt; findOrdersByCustomerAndStore(final CustomerModel customerModel, final BaseStoreModel store,
              final OrderStatus[] status, final PageableData pageableData)
      {
          validateParameterNotNull(customerModel, "Customer must not be null");
          validateParameterNotNull(store, "Store must not be null");
  
          final Map&lt;String, Object&gt; queryParams = new HashMap&lt;String, Object&gt;();
          queryParams.put("customer", customerModel);
          queryParams.put("store", store);
  
          String filterClause = StringUtils.EMPTY;
          if (CollectionUtils.isNotEmpty(getFilterOrderStatusList()))
          {
              queryParams.put("filterStatusList", getFilterOrderStatusList());
              filterClause = FILTER_ORDER_STATUS;
          }
  
          final List&lt;SortQueryData&gt; sortQueries;
  
          if (ArrayUtils.isNotEmpty(status))
          {
              queryParams.put("statusList", Arrays.asList(status));
              sortQueries = Arrays.asList(
                      createSortQueryData("byDate",
                              createQuery(FIND_ORDERS_BY_CUSTOMER_STORE_QUERY_AND_STATUS, filterClause, SORT_ORDERS_BY_DATE)),
                      createSortQueryData("byOrderNumber",
                              createQuery(FIND_ORDERS_BY_CUSTOMER_STORE_QUERY_AND_STATUS, filterClause, SORT_ORDERS_BY_CODE)));
          }
          else
          {
              sortQueries = Arrays
                      .asList(
                              createSortQueryData("byDate",
                                      createQuery(FIND_ORDERS_BY_CUSTOMER_STORE_QUERY, filterClause, SORT_ORDERS_BY_DATE)),
                              createSortQueryData("byOrderNumber",
                                      createQuery(FIND_ORDERS_BY_CUSTOMER_STORE_QUERY, filterClause, SORT_ORDERS_BY_CODE)));
          }
          }
  
          return getPagedFlexibleSearchService().search(sortQueries, "byDate", queryParams, pageableData);
      }
  
  DefaultCustomerAccountService   getOrderList
  
  public SearchPageData&lt;OrderModel&gt; getOrderList(final CustomerModel customerModel, final BaseStoreModel store,
              final OrderStatus[] status, final PageableData pageableData)
      {
          validateParameterNotNull(customerModel, "Customer model cannot be null");
          validateParameterNotNull(store, "Store must not be null");
          validateParameterNotNull(pageableData, "PageableData must not be null");
          return getCustomerAccountDao().findOrdersByCustomerAndStore(customerModel, store, status, pageableData);
      }
  
  DefaultOrderFacade  getPagedOrderHistoryForStatuses
  
  public SearchPageData&lt;OrderHistoryData&gt; getPagedOrderHistoryForStatuses(final PageableData pageableData,
              final OrderStatus... statuses)
      {
          final CustomerModel currentCustomer = (CustomerModel) getUserService().getCurrentUser();
          final BaseStoreModel currentBaseStore = getBaseStoreService().getCurrentBaseStore();
          final SearchPageData&lt;OrderModel&gt; orderResults = getCustomerAccountService().getOrderList(currentCustomer, currentBaseStore,
                  statuses, pageableData);
  
          return convertPageData(orderResults, getOrderHistoryConverter());
      }
  
  AccountPageController   orders
  public String orders(@RequestParam(value = "page", defaultValue = "0") final int page,
              @RequestParam(value = "show", defaultValue = "Page") final ShowMode showMode,
              @RequestParam(value = "sort", required = false) final String sortCode, final Model model) throws CMSItemNotFoundException
      {
          // Handle paged search results
          final PageableData pageableData = createPageableData(page, 5, sortCode, showMode);
          final SearchPageData&lt;OrderHistoryData&gt; searchPageData = vinOrderFacade.getPagedOrderHistoryForStatuses(pageableData);
          populateModel(model, searchPageData, showMode);
  
          storeCmsPageInModel(model, getContentPageForLabelOrId(ORDER_HISTORY_CMS_PAGE));
          setUpMetaDataForContentPage(model, getContentPageForLabelOrId(ORDER_HISTORY_CMS_PAGE));
          model.addAttribute(BREADCRUMBS_ATTR, accountBreadcrumbBuilder.getBreadcrumbs("text.account.orderHistory"));
          model.addAttribute(ThirdPartyConstants.SeoRobots.META_ROBOTS, ThirdPartyConstants.SeoRobots.NOINDEX_NOFOLLOW);
          model.addAttribute("isASM", assistedServiceFacade.isAssistedServiceAgentLoggedIn());
          return getViewForPage(model);
      }
  </code></pre>