Java – flexible search with parameters returns a null value

I have to perform this flexible search query in the 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 execute this query in the hybris administration console, I correctly get:

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,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(")");
        }

    }

}

Search (query) The query string before 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? What's wrong with my java code? thank you.

Solution

In addition, if you want to disable restrictions in Java code You can do this

@Autowired
private SearchRestrictionService searchRestrictionService;

private BigDecimal findGroupedOrdersData(String total,Object> queryParameters) {

  searchRestrictionService.disableSearchRestrictions();

  // You code here

  searchRestrictionService.enableSearchRestrictions();
  return aggregatedValue;
}

In the above code, you can disable the search restriction and enable it again after the search results

or

You can use sessionservice to execute flexible search queries in local view The executeinlocalview method can be used to execute code in an isolated session

(SearchResult<? extends ItemModel>) sessionService.executeInLocalView(new SessionExecutionBody()
{
   @Override
   public Object execute() 
   {
     sessionService.setAttribute(FlexibleSearch.DISABLE_RESTRICTIONS,Boolean.TRUE);
     return flexibleSearchService.search(query);
   }
});

Here, you will set disable restrictions = true, which will run the query in the administrative context [unlimited]

Check this

Better, I suggest you check the restrictions on your project type Just log in to backoffice / HMC

backstage:

>Go to system – > search restriction > search by restricted type > check filter query and analyze your project data based on this data. > You can also check the principal (user group) of which restriction is applied. > To confirm, simply check by disabling the activity flag