3.1.4.1.5. Ordering specifying
Ordering by property

Select all nodes with the 'mix:title' mixin type and order them by the 'prop_pagecount' property.

Repository structure

The repository contains several mix:title nodes, where 'prop_pagecount' has different values.

  • root

    • document1 (mix:title) jcr:title="War and peace" jcr:description="roman" prop_pagecount=4

    • document2 (mix:title) jcr:title="Cinderella" jcr:description="fairytale" prop_pagecount=7

    • document3 (mix:title) jcr:title="Puss in Boots" jcr:description="fairytale" prop_pagecount=1

Query execution

  • SQL

    // make SQL query
    
    QueryManager queryManager = workspace.getQueryManager();
    // create query
    String sqlStatement = "SELECT * FROM mix:title ORDER BY prop_pagecount ASC";
    Query query = queryManager.createQuery(sqlStatement, Query.SQL);
    // execute query and fetch result
    QueryResult result = query.execute();
  • XPath

    // make XPath query
    
    QueryManager queryManager = workspace.getQueryManager();
    // create query
    String xpathStatement = "//element(*,mix:title) order by @prop_pagecount ascending";
    Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
    // execute query and fetch result
    QueryResult result = query.execute();

Fetching result

Let's get nodes:

NodeIterator it = result.getNodes();


if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

The NodeIterator will return nodes in the following order "document3", "document1", "document2".

You can also get a table:

String[] columnNames = result.getColumnNames();

RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Table content is:

jcr:titlejcr:descriptionprop_pagecountjcr:pathjcr:score
Puss in Bootsfairytale1/document31405
War and peaceroman4/document11405
Cinderellafairytale7/document21405

Ordering by descendant node property

Find all nodes with the 'nt:unstructured' primary type and sort them by the property value of descendant nodes with the relative path '/a/b'.

Note

This ORDER BY construction only works in XPath.

Repository structure

  • root

    • node1 (nt:unstructured)

      • a (nt:unstructured)

        • b (nt:unstructured)

    • node2 (nt:unstructured)

      • a (nt:unstructured)

        • b (nt:unstructured)

          • c (nt:unstructured) prop = "a"

    • node3 (nt:unstructured)

      • a (nt:unstructured)

        • b (nt:unstructured)

          • c (nt:unstructured) prop = "b"

Query execution

  • XPath

    // make XPath query
    
    QueryManager queryManager = workspace.getQueryManager();
    // create query
    String xpathStatement = "/jcr:root/* order by a/b/c/@prop descending;
    Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
    // execute query and fetch result
    QueryResult result = query.execute();

Fetching result

Let's get nodes:

NodeIterator it = result.getNodes();


if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

NodeIterator will return nodes in the following order - "node3","node2" and "node1".

You can also get a table:

String[] columnNames = result.getColumnNames();

RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Table content is:

jcr:primaryTypejcr:pathjcr:score
nt:unstructured/testroot/node31000
nt:unstructured/testroot/node21000
nt:unstructured/testroot/node11000

Ordering by score

Select all nodes with the mixin type 'mix:title' containing any word from the set {'brown','fox','jumps'}. Then, sort result by the score in ascending node. This way nodes that match better the query statement are ordered at the last positions in the result list.

Info

SQL and XPath queries support both score constructions: jcr:score and jcr:score().

SELECT * FROM nt:base ORDER BY jcr:score [ASC|DESC]
SELECT * FROM nt:base ORDER BY jcr:score()[ASC|DESC]

//element(*,nt:base) order by jcr:score() [descending]
//element(*,nt:base) order by @jcr:score [descending]

Do not use "ascending" combined with jcr:score in XPath. The following XPath statement may throw an exception:

... order by jcr:score() ascending

Do not set any ordering specifier - ascending is default:

... order by jcr:score()

Repository structure

The repository contains mix:title nodes, where the jcr:description has different values.

  • root

    • document1 (mix:title) jcr:description="The quick brown fox jumps over the lazy dog."

    • document2 (mix:title) jcr:description="The brown fox lives in the forest."

    • document3 (mix:title) jcr:description="The fox is a nice animal."

Query execution

  • SQL

    // make SQL query
    
    QueryManager queryManager = workspace.getQueryManager();
    // create query
    String sqlStatement = "SELECT * FROM mix:title WHERE CONTAINS(*, 'brown OR fox OR jumps') ORDER BY jcr:score() ASC";
    Query query = queryManager.createQuery(sqlStatement, Query.SQL);
    // execute query and fetch result
          QueryResult result = query.execute(); 
  • XPath

    // make XPath query
    
    QueryManager queryManager = workspace.getQueryManager();
    // create query
    String xpathStatement = "//element(*,mix:title)[jcr:contains(., 'brown OR fox OR jumps')] order by jcr:score()";
    Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
    // execute query and fetch result
    QueryResult result = query.execute();

Fetching result

Let's get nodes

NodeIterator it = result.getNodes();


if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

NodeIterator will return nodes in the following order: "document3", "document2", "document1".

You can also get a table:

String[] columnNames = result.getColumnNames();

RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Table content is:

jcr:description...jcr:pathjcr:score
The fox is a nice animal..../document32512
The brown fox lives in the forest..../document23595
The quick brown fox jumps over the lazy dog..../document15017

Ordering by path or name

Warning

Ordering by jcr:path or jcr:name does not supported.

There are two ways to order results, when path may be used as criteria:

  • Order by property with the NAME or PATH value type (JCR supports it)

  • Order by jcr:path or jcr:name - sort by the exact path or name of node (JCR does not support it).

If no order specification is supplied in the query statement, implementations may support document order on the result nodes (see the 6.6.4.2 Document Order section of JSR-170), and it is sorted by order number.

By default, (if query does not contain any ordering statements) result nodes are sorted by document order.

SELECT * FROM nt:unstructured WHERE jcr:path LIKE 'testRoot/%'
Copyright ©. All rights reserved. eXo Platform SAS
blog comments powered byDisqus