3.1.4.1.5. Ordering specifying

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

Ordering by property

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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:title	jcr:description	prop_pagecount	jcr:path	jcr:score
Puss in Boots	fairytale	1	/document3	1405
War and peace	roman	4	/document1	1405
Cinderella	fairytale	7	/document2	1405

Ordering by descendant node property

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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:primaryType	jcr:path	jcr:score
nt:unstructured	/testroot/node3	1000
nt:unstructured	/testroot/node2	1000
nt:unstructured	/testroot/node1	1000

Ordering by score

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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:path	jcr:score
The fox is a nice animal.	...	/document3	2512
The brown fox lives in the forest.	...	/document2	3595
The quick brown fox jumps over the lazy dog.	...	/document1	5017

Ordering by path or name

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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/%'