orderby.sort value not being reflected in generated xpath

[benorosz]

I'm having differing results when running a query via the query builder and via a component building the same query, mainly on the order of the results. Setting orderby.sort = desc in the query builder gives me exactly as I would expect, in this case ordering results by publication date starting with the most recent. However, when I add the exact same predicate to a query inside a component, the xpath that gets generated is missing the "descending" at the end and thus the results are order in ascending order.

The snippet that adds the orderby.sort predicate:

.set( "orderby.sort", request.getResource().getValueMap().get( "sortOrder", String.class ) );

The component in question is based largely on the Core List v4, so the possible values of the sortOrder property are "asc" and "desc", which should line up with possible values for the same predicate in the query builder.

The xpath that results from the query builder is 

/jcr:root/content/[site-root]//element(*, cq:Page) [(jcr:content/@cq:tags = '[tag-path]')] order by jcr:content/@pubdate descending

result.getQueryStatement() inside the component returns 

/jcr:root/content/[site-root]//element(*, cq:Page)[(jcr:content/@cq:tags = '[tag-path]')]/rep:excerpt(.) order by jcr:content/@pubdate

For some reason, the orderby.sort=desc doesn't get picked up even though I both see it as a predicate on the SimpleSearch before it is run and the other sorting predicate, orderby, is added in the same line as orderby.sort and takes effect as expected.

[Kamal_Kishor]

@benorosz : Can you please try to print the value of "sortOrder" field property to see if it is returning the value that you are expecting i.e desc.

request.getResource().getValueMap().get( "sortOrder", String.class);
You can refer this sheet for other querybuilder related quickhelps : https://github.com/paulrohrbeck/aem-links/blob/master/querybuilder_cheatsheet.md

If this does not work, please share a bit more of your code snippet as it is not clear on how you are building your query in component.

thanks.

[benorosz]

I've already checked, it is "desc" just as expected. I've also tried "dec" and "DESC" just to see, but neither have worked.

The full creation of the search is:

SimpleSearch search = request.getResource().adaptTo( SimpleSearch.class );
	if ( search != null ) {
		String query = this.queryParams.get( "query" );
		if ( !StringUtils.isEmpty( query ) ) {
			search.setQuery( query );
		}

		search.setSearchIn( searchRoot.get().getPath() );
		search.addPredicate( new Predicate( "type", "type" ).set( "type", NameConstants.NT_PAGE ) );

		  // Filtering / sorting predicates
		search = Filtering.addTagPredicates( request, search, this.queryParams, this.indexFiltering, this.tabsBy );
		search = Sorting.addSortPredicate( request, search );

		  // Pagination settings
		if ( this.indexPagination == true ) {
			search.setHitsPerPage( this.pagination.getPageSize() );
			search.setStart( this.pagination.getLowerBound() );
		} else {
			search.setHitsPerPage( 0 );
		}

		Optional<SearchResult> result = safeGetSearchResult( search );
	}
}

with Sorting.addSortPredicate():

public static SimpleSearch addSortPredicate( SlingHttpServletRequest request, SimpleSearch search ) {
    String orderBy = request.getResource().getValueMap().get( "orderBy", String.class );
    if ( !StringUtils.isEmpty( orderBy ) ) {
        Predicate sortPred = new Predicate( "orderby" )
            .set( "orderby", "@jcr:content/" + orderBy )
            .set( "orderby.sort", request.getResource().getValueMap().get( "sortOrder", String.class ) );

        search.addPredicate( sortPred );
    }
    return search;
}

I tried orderby.desc=true based on your cheatsheet, but that didn't work either. The search object in the debugger has the predicate at time of evaluation:

but the resulting result.getQueryStatement() is still just 

/jcr:root/content/[site-root]//element(*, cq:Page)[(jcr:content/@cq:tags = '[tag-path]')]/rep:excerpt(.) order by jcr:content/@pubdate

[Sudheer_Sundalam]

@benorosz ,

Just to debug the issue further, try hard coding the "desc" value while setting the "oderby.sort"

.set( "orderby.sort", "desc" );

This provides the clarity on whether the query being built using the component code is correct or not. If the query is building as expected using the hardcoded value of "desc", then the function call to 

request.getResource().getValueMap().get( "sortOrder", String.class )

might not be returning the value as expected and you need to look further into component node under /content to check how the value is stored and why it is not returning as expected by the above call.

[benorosz]

I've not hardcoded it in the code itself, but I have tried that while debugging and manually setting values while the code is halted. I've also tried "dec", which I saw either here or StackOverflow and wasn't sure if it was a typo or not, and "DESC" in case case mattered, but neither solved the issue.

[Kamal_Kishor]

@benorosz : If you are still seeing the issue, would you mind trying with below :

    // create query description as hash map (simplest way, same as form post)
    Map<String, String> map = new HashMap<String, String>();

// create query description as hash map (simplest way, same as form post)
    map.put("path", "/content/xyz");
    map.put("type", "cq:Page");
//plugin other filter conditions as per your need.
    map.put("orderby", request.getResource().getValueMap().get( "orderBy", String.class ));
    map.put("orderby.sort", request.getResource().getValueMap().get( "sortOrder", String.class ));
    map.put("p.limit", "20"); // same as query.setHitsPerPage(20) below

    Query query = builder.createQuery(PredicateGroup.create(map), session);
    query.setStart(0);
    query.setHitsPerPage(20);

    SearchResult result = query.getResult();

    // paging metadata
    int hitsPerPage = result.getHits().size(); // 20 (set above) or lower
    long totalMatches = result.getTotalMatches();
    long offset = result.getStartIndex();
    long numberOfPages = totalMatches / 20;

    //Place the results in XML to return to client
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    DocumentBuilder builder = factory.newDocumentBuilder();
    Document doc = builder.newDocument();

    //Start building the XML to pass back to the AEM client
    Element root = doc.createElement( "results" );
    doc.appendChild( root );

    // iterating over the results
    for (Hit hit : result.getHits()) {
       String path = hit.getPath();

      //Create a result element
      Element resultel = doc.createElement( "result" );
      root.appendChild( resultel );

      Element pathel = doc.createElement( "path" );
      pathel.appendChild( doc.createTextNode(path ) );
      resultel.appendChild( pathel );
    }

refer: https://docs.mktossl.com/docs/experience-manager-cloud-service/content/implementing/developing/full-...

Also, as you said if this works fine in querybuilder and not with your query created in your backend code, the issue is with the way you are making this sort predicate and then adding it to main query.

[kautuk_sahni]

@benorosz Did you find the suggestions from users helpful? Please let us know if more information is required. Otherwise, please mark the answer as correct for posterity. If you have found out solution yourself, please share it with the community.

[benorosz · Accepted solution]

I apologize for not being able to get back to this until now. None of the suggested solutions worked for me, but I did end up finding one that worked. Instead of setting orderby.sort to desc as I had, turns out since I set the predicate type to orderby in its constructor, to set the sort order I just need to set sort.

Predicate sortPred = new Predicate( "orderby" )
    .set( "orderby", "@jcr:content/" + orderBy )
    .set( "sort", request.getResource().getValueMap().get( "sortOrder", String.class ) );

I didn't find anything in documentation or any other questions here or anywhere else to suggest this, I just had a hunch. Everything I found said to use orderby.sort.