Querying: Sorting
Basics
Starting from RavenDB 4.0, the server will determine possible sorting capabilities automatically from the indexed value, but sorting will not be applied until you request it by using the appropriate methods. The following queries will not return ordered results:
List<Product> results = session
.query(Product.class, Products_ByUnitsInStock.class)
.whereGreaterThan("UnitsInStock", 10)
.toList();
public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
public Products_ByUnitsInStock() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
"})";
}
}
from index 'Products/ByUnitsInStock'
where UnitsInStock > 10
To start sorting, we need to request to order by some specified index field. In our case we will order by UnitsInStock
in descending order:
List<Product> results = session
.query(Product.class, Products_ByUnitsInStock.class)
.whereGreaterThan("UnitsInStock", 10)
.orderByDescending("UnitsInStock")
.toList();
public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
public Products_ByUnitsInStock() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
"})";
}
}
from index 'Products/ByUnitsInStock'
where UnitsInStock > 10
order by UnitsInStock as long desc
Forcing ordering type
OrderingType
can be forced by passing OrderingType
explicitly to one of the orderBy
methods.
List<Product> results = session
.query(Product.class, Products_ByUnitsInStock.class)
.whereGreaterThan("UnitsInStock", 10)
.orderByDescending("UnitsInStock", OrderingType.STRING)
.toList();
public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
public Products_ByUnitsInStock() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
"})";
}
}
from index 'Products/ByUnitsInStock'
where UnitsInStock > 10
order by UnitsInStock desc
Ordering by Score
When a query is issued, each index entry is scored by Lucene (you can read more about Lucene scoring here) and this value is available in metadata information of a document under @index-score
(the higher the value, the better the match). To order by this value you can use the orderByScore
or the orderByScoreDescending
methods:
List<Product> results = session
.query(Product.class, Products_ByUnitsInStock.class)
.whereGreaterThan("UnitsInStock", 10)
.orderByScore()
.toList();
public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
public Products_ByUnitsInStock() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
"})";
}
}
from index 'Products/ByUnitsInStock'
where UnitsInStock > 10
order by score()
Chaining Orderings
It is also possible to chain multiple orderings of the query results.
You can sort the query results first by some specified index field (or by the @index-score
), then sort all the equal entries by some different index field (or the @index-score
).
This can be achieved by using the orderBy
(orderByDescending
) and orderByScore
(orderByScoreDescending
) methods.
List<Product> results = session
.query(Product.class, Products_ByUnitsInStockAndName.class)
.whereGreaterThan("UnitsInStock", 10)
.orderBy("UnitsInStock")
.orderByScore()
.orderByDescending("Name")
.toList();
public static class Products_ByUnitsInStockAndName extends AbstractIndexCreationTask {
public Products_ByUnitsInStockAndName() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
" Name = product.Name" +
"})";
}
}
from index 'Products/ByUnitsInStockAndName'
where UnitsInStock > 10
order by UnitsInStock, score(), Name desc
Random Ordering
If you want to randomize the order of your results each time the query is executed, use the randomOrdering
method (API reference here):
List<Product> results = session
.query(Product.class, Products_ByUnitsInStock.class)
.randomOrdering()
.whereGreaterThan("UnitsInStock", 10)
.toList();
public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
public Products_ByUnitsInStock() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
"})";
}
}
from index 'Products/ByUnitsInStock'
where UnitsInStock > 10
order by random()
Ordering When a Field is Searchable
When sorting must be done on field that is Searchable, due to Lucene limitations sorting on such a field is not supported. To overcome this, create another field that is not searchable, and sort by it.
List<Product> results = session
.query(Product.class, Products_ByName_Search.class)
.search("Name", "Louisiana")
.orderByDescending("NameForSorting")
.toList();
public static class Products_ByName_Search extends AbstractIndexCreationTask {
public static class Result {
private String name;
private String nameForSorting;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getNameForSorting() {
return nameForSorting;
}
public void setNameForSorting(String nameForSorting) {
this.nameForSorting = nameForSorting;
}
}
public Products_ByName_Search() {
map = "docs.Products.Select(product => new {" +
" Name = product.Name," +
" NameForSorting = product.Name" +
"})";
index("Name", FieldIndexing.SEARCH);
}
}
from index 'Products/ByName/Search'
where search(Name, 'Louisiana')
order by NameForSorting desc
AlphaNumeric Ordering
Sometimes when ordering strings, it doesn't make sense to use the default lexicographic ordering.
For example, "Abc9" will come after "Abc10" because if treated as single characters, 9 is greater than 1.
If you want digit characters in a string to be treated as numbers and not as text, you should use alphanumeric ordering. In that case, when comparing "Abc10" to "Abc9", the digits 1 and 0 will be treated as the number 10 which will be considered greater than 9.
To order in this mode you can pass the OrderingType.ALPHA_NUMERIC
type into orderBy
or orderByDescending
:
List<Product> results = session
.query(Product.class, Products_ByUnitsInStock.class)
.whereGreaterThan("UnitsInStock", 10)
.orderBy("Name", OrderingType.ALPHA_NUMERIC)
.toList();
public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
public Products_ByUnitsInStock() {
map = "docs.Products.Select(product => new {" +
" UnitsInStock = product.UnitsInStock" +
"})";
}
}
from index 'Products/ByUnitsInStock '
where UnitsInStock > 10
order by Name as alphanumeric