Skip to Navigation
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);
}
}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 use the AlphaNumericOrdering method:
IList<Product> results = session
.Query<Product, Products_ByUnitsInStock>()
.Customize(x => x.AlphaNumericOrdering<Product>(y => y.Name))
.Where(x => x.UnitsInStock > 10)
.ToList();IList<Product> results = session
.Advanced
.DocumentQuery<Product, Products_ByUnitsInStock>()
.AlphaNumericOrdering<Product>(x => x.Name)
.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.AlphaNumericFieldName + ";" + nameof(Product.Name))
}
});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);
}
}
Related Articles