Unsolvable events scenario in RavenDB?

Edit. Here's a simplified description of the issue: I have events

class Event { Id = 0, Dates = new DateTime[] {} }

I need to query for all events within a date range for example (august 1 to october 20). The result shall list uniqe events within this range ordered by date. Like this:

Event one   2012-08-04,2012-09-06,2012-09-10
Event two   2012-10-02
etc.

I need to be able to page this result. That's it.


I have the following issue with my events using ravendb. I have a document (representing an event) that contains an array of dates, for example 2012-08-20, 2012-08-21, 2012-09-14, 2013-01-05 etc.

class Event { Dates = []; }

I have a few criteria that must be met:

  1. I need to be able to query these documents on a date range. For example find all events that has any date between august 1 and september 22, or october 1 and october 3.

  2. I must be able to sort the query on date

  3. I must be able to page the result.

Seems easy enough right? Well I have had two approaches to this but they both fail.

Create an index with multiple from. Like so:

from event in docs.Events
from date in event.Dates
select new { Dates = date}

This works and is easy to query. However it can't be paged because of skippedresults (the index will contain duplicates of each event). And sorting also fails in combination with paging.

...............

Create a simple index

from event in docs.Events
select new { Dates = event.Dates }

This also works and is simple to query, it can also be paged. However it cannot be sorted. I need to sort the documents by the first available date within the queried range.

If I can't solve this it will probably be a deal breaker for us.. and I really don't want to get started with a new application, besides I really like RavenDB..

Answers


I had a similar requirement (recurring events), with the added twist that the number of dates was highly variable (could be from 1 to the hundreds) and may be associated with a venue.

I ended up storing event dates in an EventOccurrence after coming to a similar impasse:

public class EventOccurrence {   
   public string EventId {get; set;}   
   public DateTime Start {get; set;}   
   public string VenueId {get; set;} 
}

Its easily queryable and sortable, and using Session.Include on the occurrence we still retain query performance.

It seems like a reversion to the relational model, but it was the correct choice given our constraints.


You're saying that the "simple index" approach works except for sorting right?

from event in docs.Events
select new
{
  Dates = event.Dates,
  FirstDate = event.Dates.First()
}

Then you can sort on FirstDate. You can't sort on analyzed or tokenized fields.


My final solution for this was creating both indexes. We needed both indexes anyway in our application so it wasn't any overhead.

The following index is used for querying and paging. Take() and Skip() works for this one:

from event in docs.Events
from date in event.Dates
select new { Date = date}

However the above index does NOT return the correct number of total hits, which you need for creating a pager. So we create another index:

from event in docs.Events
select new { Date = event.Dates }

Now we can run the exact same query (note that the Date field has the same name on both indexes) on the above index using Statistics() and Take(0) to only get the number of hits.

The downside to this is obviously that you need to run two queries, but I haven't found a way around that.


Need Your Help

How to create a Multiple date picker for a search form

ms-access access-vba ms-access-2010

I am quite a newbie to MS Access.I am working with a search form in MS Access and I want to create a date picker in which I can select multiple dates which will be an input for a Query that is used...

get the unid of selected documents and save to scoped variable

xpages

I've a viewPanel1 with checkboxes. By clicking on a button I would like to save the univ id's (comma seperated) of the selected items to a scoped variable. I tried this :

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.