RavenDB query a scoreboard

Question

Given a scoreboard like this: ie (same score and user allowed)

user    |  score  |  date

user1   |   1000  | 10-05-2012
user1   |    999  | 10-05-2012
user2   |    998  | 10-05-2012
user1   |    998  | 10-05-2012
user4   |    987  | 10-05-2012

I d like to query the document for a particular score and get x results below and above

for example say I want to target user2 with score 998 and get 2 above and 2 below

The date is there but doesn't influence the query, when a new score comes in and the same value existed, it would be included below.

What would be the best way to go about it?

at the moment I m doing the worse possible thing, that is to have three queries: one that gets the score I m looking for and then another two to get the some rows above and below. I've seen some discussion on the GG but I cant make anything that makes sense work, hopefully posting the question here is ok

Update: there is also this thread at the discussion group but it doesn't seem to resolve it either.

Thanks

Answer 1

Miau, You don't really have a choice, you can drop it to two queries:

var q1 = session.Query<Score>().Where(x=>x.Value >= 998).OrderBy(x=> x.Value).Take(3).ToList();
var q2 = session.Query<Score>().Where(x=>x.Value < 998).OrderByDescending(x=> x.Value).Take(3).ToList();

You can improve on that by using lazy queries:

var q1 = session.Query<Score>().Where(x=>x.Value >= 998).OrderBy(x=> x.Value).Take(3).Lazily();
var q2 = session.Query<Score>().Where(x=>x.Value < 998).OrderByDescending(x=> x.Value).Take(3).Lazily();

Now both queries will be issued against the db in a single request.