Specify document return order

[Hannes Johansson]

I want to order a set of documents based on a property that is set by an external system at runtime, when querying RavenDB. That is, I want to do something like the following:

var resultFromService = service.GetResultOrderedExternally();

var query = _session.Advanced.LuceneQuery<ResultType>("BigIndex")

 .OrderByDescending(x => x.Id == resultFromService.First().Id);

foreach (var result in resultFromService) {

         var id = result.Id;

    query = query.ThenByDescending(x => x.Id == id);

}

var orderedQueryResult = query.ToList();

But surely, there must be a prettier and more effiecient way to do this? The key issue here is that I can't index the property I want to sort by since I don't have it when the index is built.

Cheers

[Matt Warren]

You can only order the results by a value that is indexed, so you would have to add a field into the index (with the ordering from the external service) to make this work. This is because the ordering is done by Lucene as part of the query and it only has accessed to indexed values.

If this isn't possible (which seems the case) then you have to do something like the workaround you propose.

[Oren Eini (Ayende Rahien)]

Note that this workaround won't actually work, you can only index by items that are in the index, nothing else.

[Hannes Johansson]

Yes, sorry, it was probably not apparent from the code snippet, but the idea is that the external service has returned the result ordered by the desired field, and then I pick the "Id" property, which is indexed in Lucene and sort the whole result based on whether or not a product matches that Id property.

This would of course mean that I will have to order the whole thing a linear number of times, which is not exactly desirable, but I see no other workaround. It was, however, surprisingly fast, and landed somewhere around 140 ms for 30k documents on my local machine.

[Oren Eini (Ayende Rahien)]

That won't actually work.

You are basically doing a sort by id, and we collapse it all to a single .OrderBy(x=>x.Id)

You can't do ordering by an external field, because you have to be able to compare all values to all other values.

There _are_ ways to do that, but they are complex & slow.

What is the actual scenario that you are trying to use?

[Hannes Johansson]

Actually, this appears to work, but doesn't scale beyond a few thousand documents.

var ordered = result.OrderByDescending(x => x.ProductId == 3000);

for (int i = 2999; i >= 0; i--)

{

 int closureCopy = i;

ordered = ordered.ThenByDescending(x => x.ProductId == closureCopy);

}

                var res =  ordered.ToList();

The scenario is this:

I get a result from an external system, this result contains document Ids for RavenDB and live pricing information which cannot be stored in ravendb. What I want to do then is to fetch these ids in a particular order, determined by the pricing. I will also do paging on the result, which is why I cannot sort it after fetching it from RavenDB.

I've pondered taking apart the result from the external service and doing the paging myself before fetching the result from RavenDB, but I was hoping for a cleaner solution using the db. Do you see any possible solutions?

[Matt Warren]

If you have the ProductID (from the external service) in the correct order, you can just load them directly by doing this:

     var listOfIDs = new [] { "product/5", "product/1", "product/18", ..};

   var products = session.Load<Product>(listOfIDs)

[Hannes Johansson]

Yes, this does actually seem to be working for my scenario, I was not aware that the documents would return in order. Thank you both very much for your time!

[Matt Warren]

Yeah it returns them in the same order that the ID's are specified in, also it will insert a null if the doc doesn't exist. This is so you know which docs were loaded and you can match them up to the list of ID's you supplied.

Glad it worked.