Skip to Content
0
Former Member
Nov 05, 2015 at 02:56 PM

Flexible search with parameter return null

1593 Views

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.