"order by" based on a expression

Question

Given a comparator:

public int compareTo(Person p1, Person p2)
{
    String val1 = StringUtils.isEmpty(p1.nickName) ? p1.name : p1.nickName;
    String val2 = StringUtils.isBlank(p2.nickName) ? p2.name : p2.nickName;

    return val1.compareTo(val2);
}

I'd like to achieve the same result with "order by" using a xpath query (or SQL2 if it's beyond xpath's capabilities). Is it possible?

Given data:

No. |  Name    | Nickname |
 1  |  Adam    | Hornet   |
 2. |  Adam    |          |
 3. |  Jack    | Legend   |

Sorted data:

No. | name | nickname
2.  | Adam | 
1.  | Adam | Hornet
3.  | Jack | Legend

Answer 1

You can do this in both JCR-SQL2 and XPath.

Standard JCR-SQL2

Assuming you have a node type called Person with name and nickname fields, then one way to achieve something close to your desired results in JCR-SQL2 is:

SELECT p.name, p.nickname FROM Person AS p ORDER BY p.name ASC, p.nickname ASC

But we can write this query a little more plainly because there is just a single selector (i.e., a single "table"), and we can leave off the optional ASC

SELECT name, nickname FROM Person ORDER BY name, nickname

Now, strictly speaking the results you get from this query will be dependent upon the implementation, and that's because you described in your question how the nickname field might be null, and the JCR 2.0 specification does not specify how the ordering deals with NULL values. For example, should they appear before or after non-null values? The implementation gets to pick.

There's another sticky issue. Notice that I've assumed that the Person node type has a name field, rather than assume that you're storing the name of the person in the node's name. That's because in JCR-SQL2 you have to access the node's name using a special function named NODENAME or NODELOCALNAME. You can use this directly in the ordering, but unfortunately obtaining the node's name in the query results is not defined in standard JCR-SQL2.

So the following query using standard JCR-SQL2 would work:

SELECT nickname FROM Person ORDER BY NODELOCALNAME, nickname

but you'd have to get the node's name from the javax.jcr.Node obtained from the results rather than one of the values in the result set. Strictly speaking, this is neither expensive nor difficult; it just changes how your application uses the query results.

JCR-SQL2 in ModeShape

One JCR implementation, ModeShape, extends the standard JCR-SQL2 grammar with a number of additional features. One of those extensions is to introduce several pseudo-columns, including:

• jcr:name is a stand-in for the node's name
• jcr:path is a stand-in for the node's path
• mode:localName is a stand-in for the local part of the node's name
• mode:depth evaluates to the depth of the node
• jcr:uuid evaluates to the result of javax.jcr.Node.getUUID()

So you can use the jcr:name pseudo-column both in the ordering and in the SELECT clause:

SELECT jcr:name, nickname FROM Person ORDER BY jcr:name, nickname

Those options are available in ModeShape 3.x. But the community is currently working on ModeShape 4.0 (currently in Alpha), and one more extension has been added: you can specify whether nulls should come first or last in an ordering. Here's a variation of the earlier query that shows how to use it:

SELECT name, nickname FROM Person ORDER BY name NULLS FIRST, nickname NULLS FIRST

If you're Person node type definition ensured that the name property were mandatory (never null), you wouldn't need to specify the NULLS FIRST (or NULLS LAST) on the name column ordering.

Here's the same query except with jcr:name rather than name:

SELECT jcr:name, nickname FROM Person ORDER BY jcr:name, nickname NULLS FIRST

Notice that we didn't need to specify NULLS FIRST or NULLS LAST on the jcr:name column, since every node has a name and thus jcr:name will never be null.

Standard JCR 1.0 XPath

If you're using a JCR implementation that still supports XPath (which technically was specified as part of JSR-170, or JCR 1.0, and was deprecated in JSR-283, or JCR 2.0), you can also use XPath:

//element(*, Person) order by @name ascending, @nickname ascending

Since JCR 1.0 does define the jcr:name pseudo-column, you can actually pretty easily use the node's name:

//element(*, Person) order by @jcr:name ascending, @nickname ascending

One more thing: ModeShape 3.x or later parses queries written in JCR-SQL2, XPath, and JCR-SQL into the same abstract syntax tree and then executes them. Therefore, the last JCR-SQL2 query and last XPath query would be processed identically in ModeShape.

Answer 2

Here's a sketch of how you can do it with arbitrary code in your comparator. Take the NodeIterator that you get from executing an ordinary query:

import javax.jcr.query.Query;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;
import javax.jcr.query.NodeIterator;
import org.apache.jackrabbit.commons.iterator.NodeIteratorAdapter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

. . .

Query query = queryManager.createQuery( /* query without any ordering specs */ );
QueryResult queryResult = query.execute();
NodeIterator nodeIterator = queryResult.getNodes();

and pass it to a method that performs the sort and returns another NodeIterator:

NodeIterator sortedNodes = sortMyWay(nodeIterator);

. . .

private NodeIterator sortMyWay(NodeIterator input) {
    ArrayList<Node> results = new ArrayList<Node>();
    while (input.hasNext()) {
        results.add(input.nextNode());
    }
    Collections.sort(results, new Comparator<Node>(){
        @Override
        public int compare(Node node1, Node node2) {
            String nickName1 = (node1.hasProperty("nickName") ? node1.getProperty("nickName").getString() : "");
            String nickName2 = (node2.hasProperty("nickName") ? node2.getProperty("nickName").getString() : "");
            return nickName1.compareTo(nickName2);
        }
    });
    return new NodeIteratorAdapter(results);
}

Retrieve your results from sortedNodes.nextNode() as usual.

Add exception handling to the above method as needed, and add defaulting to the name property as needed.