Flexible search with parameter return null

[Former Member]

I have to do this flexible search query in a service Java class:

 select sum({oe:totalPrice}) 
 from {Order as or join CustomerOrderStatus as os on {or:CustomerOrderStatus}={os:pk} 
 join OrderEntry as oe on {or.pk}={oe.order}} 
 where {or:versionID} is null and {or:orderType} in (8796093066999) 
 and {or:company} in (8796093710341) 
 and {or:pointOfSale} in (8796097413125) 
 and {oe:ecCode} in ('13','14') 
 and {or:yearSeason} in (8796093066981) 
 and {os:code} not in ('CANCELED', 'NOT_APPROVED')

When I perform this query in the hybris administration console I correctly obtain:

1164.00000000

In my Java service class I wrote this:

 private BigDecimal findGroupedOrdersData(String total, String uncDisc, String orderPromo,
         Map<String, Object> queryParameters) {
 
     BigDecimal aggregatedValue = new BigDecimal(0);
 
     final StringBuilder queryBuilder = new StringBuilder();
     queryBuilder.append("select sum({oe:").append(total).append("})");
     queryBuilder.append(
             " from {Order as or join CustomerOrderStatus as os on {or:CustomerOrderStatus}={os:pk} join OrderEntry as oe on {or.pk}={oe.order}}");
     queryBuilder.append(" where {or:versionID} is null");
     if (queryParameters != null && !queryParameters.isEmpty()) {
         appendWhereClausesToBuilder(queryBuilder, queryParameters);
     }
     queryBuilder.append(" and {os:code} not in ('");
     queryBuilder.append(CustomerOrderStatus.CANCELED.getCode()).append("', ");
     queryBuilder.append("'").append(CustomerOrderStatus.NOT_APPROVED.getCode()).append("')");
     FlexibleSearchQuery query = new FlexibleSearchQuery(queryBuilder.toString(), queryParameters);
     List<BigDecimal> result = Lists.newArrayList();
     query.setResultClassList(Arrays.asList(BigDecimal.class));
     result = getFlexibleSearchService().<BigDecimal> search(query).getResult();
     if (!result.isEmpty() && result.get(0) != null) {
         aggregatedValue = result.get(0);
     }
     return aggregatedValue;
 }
 
 private void appendWhereClausesToBuilder(StringBuilder builder, Map<String, Object> params) {
 
     if ((params == null) || (params.isEmpty()))
         return;
     for (String paramName : params.keySet()) {
         builder.append(" and ");
         if (paramName.equalsIgnoreCase("exitCollection")) {
             builder.append("{oe:ecCode}").append(" in (?").append(paramName).append(")");
         } else {
             builder.append("{or:").append(paramName).append("}").append(" in (?").append(paramName).append(")");
         }
 
     }
 
 }

The query string before the search(query).getResult() function is:

 query: [select sum({oe:totalPrice}) from {Order as or join CustomerOrderStatus as os on {or:CustomerOrderStatus}={os:pk} 
 join OrderEntry as oe on {or.pk}={oe.order}} where {or:versionID} is null
 and {or:orderType} in (?orderType) and {or:company} in (?company) 
 and {or:pointOfSale} in (?pointOfSale) and {oe:ecCode} in (?exitCollection) 
 and {or:yearSeason} in (?yearSeason) and {os:code} not in ('CANCELED', 'NOT_APPROVED')], 
 query parameters: [{orderType=OrderTypeModel (8796093230839), 
 pointOfSale=B2BUnitModel (8796097413125), company=CompanyModel (8796093710341), 
 exitCollection=[13, 14], yearSeason=YearSeasonModel (8796093066981)}]

but after the search(query) result is [null]. Why? Where I wrong in the Java code? Thanks.

[cieslo]

Hi Irene,

are you sure that the second query (from Java service class) should return any value? Because in your first flexible search query (the one that returns value) you have Order.OrderType with PK = 8796093066999 and in the second with 8796093230839. Maybe you should check with the same PK (the one that returns value in administration console). Another question - are you sure that running the second query from code you have the same user in the session context like in the first query (maybe there is some PersonalizationRule restriction)?

[former_member537989]

Under which user do you execute Java code? Because in HAC you are executing queries as admin, however in Java code most probably as anonymous, thus if there is any restriction applied, you will get different queries and corresponding results...

[Former Member]

You should set current user as follows before executing the query: userService.setCurrentUser(userService.getUserForUID("The-same-User-account-that-you-used-to-login-to-hAC"));