Flexible search with parameters return null value

Question

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.

Answer 1

In addition, if you want to disable restriction in your java code. You can do like this ..

@Autowired
private SearchRestrictionService searchRestrictionService;

private BigDecimal findGroupedOrdersData(String total, String uncDisc, String orderPromo,
    Map<String, Object> queryParameters) {

  searchRestrictionService.disableSearchRestrictions();

  // You code here

  searchRestrictionService.enableSearchRestrictions();
  return aggregatedValue;
}

In the above code, You can disabled the search restriction and after the search result, you can again enable it.

OR

You can use sessionService to execute flexible search query in Local View. The method executeInLocalView can be used to execute code within 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 are setting DISABLE RESTRICTIONS = true which will run the query in admin context [Without Restriction].

Check this

Better i would suggest you to check what restriction exactly applying to your item type. You can simply check in Backoffice/HMC

Backoffice :

1. Go to System-> Personalization (SearchRestricion)
2. Search by Restricted Type
3. Check Filter Query and analysis your item data based on that.
4. You can also check its Principal (UserGroup) on which restriction applied.
5. To confirm, just check by disabling active flag.