Sorting
Note
This article focuses only on querying side of the sorting. If you are interested in reading how to create indexes and change default sorting behavior go here.
Basics
By default, all index values are sorted lexicographically, this can be changed in index definition, but sorting is not applied until you request it by using appropriate methods, so following queries will not return sorted results, even if we define in our index appropriate sorting option:
IList<Product> results = session
.Query<Product, Products_ByUnitsInStock>()
.Where(x => x.UnitsInStock > 10)
.ToList();
IList<Product> results = session
.Advanced
.DocumentQuery<Product, Products_ByUnitsInStock>()
.WhereGreaterThan(x => x.UnitsInStock, 10)
.ToList();
QueryResult result = store
.DatabaseCommands
.Query(
"Products/ByUnitsInStock",
new IndexQuery
{
Query = "UnitsInStock_Range:{Ix10 TO NULL}"
});
public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
public Products_ByUnitsInStock()
{
Map = products => from product in products
select new
{
product.UnitsInStock
};
Sort(x => x.UnitsInStock, SortOptions.Int);
}
}
So, 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:
IList<Product> results = session
.Query<Product, Products_ByUnitsInStock>()
.Where(x => x.UnitsInStock > 10)
.OrderByDescending(x => x.UnitsInStock)
.ToList();
IList<Product> results = session
.Advanced
.DocumentQuery<Product, Products_ByUnitsInStock>()
.WhereGreaterThan(x => x.UnitsInStock, 10)
.OrderByDescending(x => x.UnitsInStock)
.ToList();
QueryResult result = store
.DatabaseCommands
.Query(
"Products/ByUnitsInStock",
new IndexQuery
{
Query = "UnitsInStock_Range:{Ix10 TO NULL}",
SortedFields = new[]
{
new SortedField("-UnitsInStock_Range")
}
});
public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
public Products_ByUnitsInStock()
{
Map = products => from product in products
select new
{
product.UnitsInStock
};
Sort(x => x.UnitsInStock, SortOptions.Int);
}
}
Convention
You probably noticed that we used -
in a name of a field passed to SortedField
that was used in commands, which means that we want to sort our results in a descending order. Using +
symbol or no prefix means that ascending sorting is requested.
Of course you can change order and field name in SortedField
later since all properties have public access.
Ordering by score
When 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 Temp-Index-Score
(the higher the value, the better the match). To order by this value you can use OrderByScore
or OrderByScoreDescending
methods:
IList<Product> results = session
.Query<Product, Products_ByName>()
.Where(x => x.UnitsInStock > 10)
.OrderByScore()
.ToList();
IList<Product> results = session
.Advanced
.DocumentQuery<Product, Products_ByUnitsInStock>()
.WhereGreaterThan(x => x.UnitsInStock, 10)
.OrderByScore()
.ToList();
QueryResult result = store
.DatabaseCommands
.Query(
"Products/ByUnitsInStock",
new IndexQuery
{
Query = "UnitsInStock_Range:{Ix10 TO NULL}",
SortedFields = new[]
{
new SortedField(Constants.TemporaryScoreValue) // Temp-Index-Score
}
});
public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
public Products_ByUnitsInStock()
{
Map = products => from product in products
select new
{
product.UnitsInStock
};
Sort(x => x.UnitsInStock, SortOptions.Int);
}
}
Random ordering
If you want to randomize the order of your results each time the query is executed you can use RandomOrdering
method (API reference here):
IList<Product> results = session
.Query<Product, Products_ByUnitsInStock>()
.Customize(x => x.RandomOrdering())
.Where(x => x.UnitsInStock > 10)
.ToList();
IList<Product> results = session
.Advanced
.DocumentQuery<Product, Products_ByUnitsInStock>()
.RandomOrdering()
.WhereGreaterThan(x => x.UnitsInStock, 10)
.ToList();
QueryResult result = store
.DatabaseCommands
.Query(
"Products/ByUnitsInStock",
new IndexQuery
{
Query = "UnitsInStock_Range:{Ix10 TO NULL}",
SortedFields = new[]
{
new SortedField(Constants.RandomFieldName + ";" + Guid.NewGuid())
}
});
public class Products_ByUnitsInStock : AbstractIndexCreationTask<Product>
{
public Products_ByUnitsInStock()
{
Map = products => from product in products
select new
{
product.UnitsInStock
};
Sort(x => x.UnitsInStock, SortOptions.Int);
}
}
Ordering when field is Analyzed
When sorting must be done on field that is marked as Analyzed then due to Lucene limitations sorting on such a field is not supported. To overcome this, the solution is to create another field that is not marked as Analyzed and sort by it.
IList<Product> results = session
.Query< Products_ByName_Search.Result, Products_ByName_Search>()
.Search(x => x.Name, "Louisiana")
.OrderByDescending(x => x.NameForSorting)
.OfType<Product>()
.ToList();
IList<Product> results = session
.Advanced
.DocumentQuery<Product, Products_ByName_Search>()
.Search("Name", "Louisiana")
.OrderByDescending("NameForSorting")
.ToList();
QueryResult result = store
.DatabaseCommands
.Query(
"Products/ByName/Search",
new IndexQuery
{
Query = "Name:Louisiana*",
SortedFields = new[]
{
new SortedField("-NameForSorting")
}
});
public class Products_ByName_Search : AbstractIndexCreationTask<Product>
{
public class Result
{
public string Name { get; set; }
public string NameForSorting { get; set; }
}
public Products_ByName_Search()
{
Map = products => from product in products
select new
{
Name = product.Name,
NameForSorting = product.Name
};
Indexes.Add(x => x.Name, FieldIndexing.Analyzed);
}
}
Custom sorting
If you want to sort using your custom algorithm you need create your own sorter that inherits from IndexEntriesToComparablesGenerator
and deploy it to plugins folder on the server.
public abstract class IndexEntriesToComparablesGenerator
{
protected IndexQuery IndexQuery;
protected IndexEntriesToComparablesGenerator(IndexQuery indexQuery)
{
IndexQuery = indexQuery;
}
public abstract IComparable Generate(IndexReader reader, int doc);
}
For example, if we want to sort by specified number of characters from an end, and we want to have an ability to specify number of characters explicitly, we can implement our sorter like this:
public class SortByNumberOfCharactersFromEnd : IndexEntriesToComparablesGenerator
{
private readonly int len;
public SortByNumberOfCharactersFromEnd(IndexQuery indexQuery)
: base(indexQuery)
{
len = IndexQuery.TransformerParameters["len"].Value<int>(); // using transformer parameters to pass the length explicitly
}
public override IComparable Generate(IndexReader reader, int doc)
{
var document = reader.Document(doc);
var name = document.GetField("FirstName").StringValue; // this field is stored in index
return name.Substring(name.Length - len, len);
}
}
And it can be used like this:
IList<Employee> results = session
.Query<Employee, Employee_ByFirstName>()
.Customize(x => x.CustomSortUsing(typeof(SortByNumberOfCharactersFromEnd).AssemblyQualifiedName, descending: true))
.AddTransformerParameter("len", 1)
.ToList();
IList<Employee> results = session
.Advanced
.DocumentQuery<Employee, Employee_ByFirstName>()
.CustomSortUsing(typeof(SortByNumberOfCharactersFromEnd).AssemblyQualifiedName, descending: true)
.SetTransformerParameters(new Dictionary<string, RavenJToken> { { "len", 1 } })
.ToList();
QueryResult result = store
.DatabaseCommands
.Query(
"Employees/ByFirstName",
new IndexQuery
{
SortedFields = new[]
{
new SortedField(
Constants.CustomSortFieldName +
"-" + // "-" - descending, "" - ascending
";" +
typeof(SortByNumberOfCharactersFromEnd).AssemblyQualifiedName)
},
TransformerParameters = new Dictionary<string, RavenJToken>
{
{ "len", 1 }
}
});
public class Employee_ByFirstName : AbstractIndexCreationTask<Employee>
{
public Employee_ByFirstName()
{
Map = employees => from employee in employees
select new
{
employee.FirstName
};
Store(x => x.FirstName, FieldStorage.Yes);
}
}