AEM Query result is not in same order

[Anderson_Hamer]

We have a JCR query which will get the result of pages based on jcr:title property. There is no issue in the query and it is working as expected. When we run the query in two different AEM versions(6.0 & 6.4) the order is not same but we see the exact result.  Both AEM versions don’t have any custom index and sorting mechanism but the end result is not having any same order. Has there any order logic implemented in AEM 6.4? What should we do in order to get the same order in AEM 6.0 and AEM 6.4? Can anyone have any idea about it?

[aemmarc]

Order isn't guaranteed.

The default search implementation of Apache Lucene returns results sorted by score (the most relevant result first), then by id (the oldest result first).

Apache Lucene - Scoring

[arunpatidar]

Please check

AEM Search

[Theo_Pendle · Accepted solution]

Hi,

There are multiple ways to query AEM. As aemmarc says, Lucene scores your results and so order can differ from instance to instance.

You can however use a JCR SQL2 query to find your content. JCR SQL2 lets you use an ORDER BY clause to sort the results according as you wish (by date created, name, etc.). If you want to learn JCR SQL2, you can find a number of references with examples here, here or here for example.

To use a JCR SQL2 programmatically from Java, follow this example I created (sorry, indentation got a little messed up in transit):

import lombok.extern.slf4j.Slf4j;
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.servlets.HttpConstants;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.osgi.framework.Constants;
import org.osgi.service.component.annotations.Component;

import javax.jcr.NodeIterator;
import javax.jcr.RepositoryException;
import javax.jcr.Session;
import javax.jcr.query.Query;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;
import javax.servlet.Servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Arrays;

@Component(service = Servlet.class,
  property = {

  Constants.SERVICE_DESCRIPTION + "=Image Viewer Servlet",
  "sling.servlet.methods=" + HttpConstants.METHOD_GET,
  "sling.servlet.paths=" + "/bin/demoServlet"
  })

@Slf4j
public class DemoServlet extends SlingAllMethodsServlet {

  private static final long serialVersionUID = 2598426539166789515L;

  @Override
  protected void doGet(final SlingHttpServletRequest request, final SlingHttpServletResponse response) throws IOException {

  final PrintWriter out = response.getWriter();

  try {

  final QueryManager queryManager = getQueryManager(request);
  out.println("No specific order:");
  printNodes(out, executeQuery(queryManager,
  "SELECT * FROM [cq:Page] AS page " +

  "WHERE ISDESCENDANTNODE ([/content/we-retail])"));
  out.println();

  out.println("Ordered by name");
  printNodes(out, executeQuery(queryManager,
  "SELECT * FROM [cq:Page] AS page " +

  "WHERE ISDESCENDANTNODE ([/content/we-retail]) " +

  "ORDER BY NAME(page)"));
  } catch (final Exception e) {

  out.println("Error during execution: ");
  out.println(e);
  Arrays.stream(e.getStackTrace()).forEach(out::println);
  }

  }

  private QueryManager getQueryManager(final SlingHttpServletRequest request) throws RepositoryException {

  return request.getResourceResolver().adaptTo(Session.class).getWorkspace().getQueryManager();
  }

  private void printNodes(final PrintWriter out, final QueryResult result) throws RepositoryException {

  final NodeIterator nodes = result.getNodes();
  while (nodes.hasNext()) {

  out.println(nodes.nextNode().getPath());
  }

  }

  private QueryResult executeQuery(final QueryManager queryManager, final String queryString) throws RepositoryException {

  final Query query = queryManager.createQuery(queryString, "JCR-SQL2");
  query.setLimit(4);
  return query.execute();
  }

}

Assuming you have the We.Retail demo content on your instance, visiting http://localhost:4502/bin/demoServlet should show you the results of two queries: one with an ORDER BY clause and one without (in this case, the name is the name of the page, ie: the leaf node name for each result):