It’s fairly common to sort query results based on a property of those results - for example, sorting a collection of Blog Posts by the publish date.
But what about when you want your user to define the ordering of items?
This is the case in Fabrik where we enable the user to control the order of projects in their portfolio. They do this within the Fabrik dashboard by dragging and dropping projects into the desired order:
Fabrik originally used MSSQL as it’s data store and to achieve user defined ordering each project had an “Index” property. When a project was moved we’d have to update all the corresponding indexes depending on whether it moved up or down in the collection:
void IMoveDirection.To(int newPosition)
{
if (position == newPosition)
return;
var item = GetCurrent();
bool movingUp = (item.Position > newPosition);
if (movingUp)
{
items.Where(i => i.Position >= newPosition && i.Position < position).Shift(1);
}
else
{
items.Where(i => i.Position <= newPosition && i.Position > position).Shift(-1);
}
item.SetPosition(newPosition);
}
As the number of projects increased it became possible for the indexes to get out of sync due to the time it took to process the multiple UPDATE statements against the database.
When we decided to move Fabrik to RavenDB we had the opportunity to improve on this.
The solution was to give every site a “Portfolio” document that stored the identifiers of all it’s projects:
sites/33/portfolio:
{
"SiteId": "sites/33",
"Projects": [
"projects/1",
"projects/2"
]
}
When a new project was created it’s ID would be added to the portfolio document. Likewise when a project was deleted we’d remove it’s ID from the portfolio document:
public HttpResponseMessage Post(int siteId, AddProjectCommand command)
{
var project = new Domain.Project(
session.GetSiteId(siteId),
command.Title,
command.Slug,
command.Summary,
command.ContentType,
command.Content,
command.Template,
command.Tags,
command.Published);
session.Store(project);
// add to portfolio
var portfolio = session.Load<Domain.Portfolio>(Domain.Portfolio.GenerateId(siteId));
portfolio.AddProject(project.Id);
return Created(Mapper.Map<Project>(project), new Uri(Url.Link(RouteNames.PortfolioProjectsRoute, new { siteId = siteId, id = project.Id.ToIntId() })));
}
User defined ordering was now both simple and fast since we only had one document to update:
/// <summary>
/// Update a projects position in the specified site portfolio.
/// </summary>
/// <example>
/// PUT /api/sites/1/portfolio
/// </example>
public void Put(int siteId, MoveProjectCommand command)
{
var portfolio = session.TryLoad<Domain.Portfolio>(Domain.Portfolio.GenerateId(siteId));
portfolio.MoveProject(session.GetStringId<Domain.Project>(command.ProjectId), command.Position);
}
Of course, maintaining the order is just one part of the story. How do we then sort our results by it?
Initially we queried the projects, then sorted by the Portfolio order:
RavenQueryStatistics stats;
var portfolio = session.TryLoad<Domain.Portfolio>(Domain.Portfolio.GenerateId(siteId));
var results = session.Advanced.LuceneQuery<Domain.Project, Projects_Search>()
.Include(p => p.CategoryId)
.Statistics(out stats)
.NotDeleted()
.FromSite(session.GetSiteId(siteId))
.If(!command.Unpublished, q => q.Published());
// order results by portfolio
var projects = portfolio.Projects.Select(id => results.FirstOrDefault(p => p.Id == id))
.Where(p => p != null)
.GetPage(command.PageIndex, command.PageSize)
.ToList();
This appeared to work fine until we hit the default page size limit in RavenDB (128) and we realized that the project query was actually being limited prior to paging (despite us not calling ToList).
A far better solution was to include the portfolio order within the Projects_Search index. Not only would this be more efficient, it also opened up the possibility of providing different sort options in the future since sorting the results could be made part of the initial query.
Fortunately as of Raven 2.0 we can now index related documents by calling LoadDocument within our index Map. Our updated Index can be seen below:
public class Projects_Search : AbstractIndexCreationTask<Project>
{
public Projects_Search()
{
Map = projects => from p in projects
let portfolio = LoadDocument<Portfolio>(string.Concat(p.SiteId, "/portfolio"))
let category = LoadDocument<PortfolioCategory>(p.CategoryId)
let ids = portfolio.Projects.ToList()
select new
{
SiteId = p.SiteId,
Slug = p.Slug,
Tags_Slugs = p.Tags.Select(t => t.Slug),
Deleted = p.Deleted,
CategoryId = p.CategoryId,
CategorySlug = category.Slug,
Published = p.Published,
Terms = new object[]
{
p.Title,
p.Summary,
category.Title,
p.Tags.Select(t => t.ToString()),
p.Content
},
PortfolioIndex = ids.IndexOf(p.Id)
};
Index("Terms", FieldIndexing.Analyzed);
}
public override IndexDefinition CreateIndexDefinition()
{
var definintion = base.CreateIndexDefinition();
definintion.SortOptions.Add("PortfolioIndex", SortOptions.Int);
return definintion;
}
}
Note that we had to call ToList on portfolio.Projects. This is because Raven’s DynamicList does not currently implement IndexOf. This will be fixed in the next build.
By loading the Portfolio document within the index we were able to obtain the PortfolioIndex for each project.
Finally our API/query was updated to use the revised index:
var projects = session.Advanced.LuceneQuery<Domain.Project, Projects_Search>()
.Include(p => p.CategoryId)
.Statistics(out stats)
.NotDeleted()
.FromSite(session.GetSiteId(siteId))
.If(!command.Unpublished, q => q.Published())
.OrderBy("PortfolioIndex")
.GetPage(command.PageIndex, command.PageSize)
.ToList();
All done in one query. Much better!