Ravendb mapreduce groups across multiple fields

Question

We have a website that contains streaming video, and we want to display three reports on most videos watched in the last week, month and year (pitched window).

We save the document in ravendb every time a video is watched:

public class ViewedContent { public string Id { get; set; } public int ProductId { get; set; } public DateTime DateViewed { get; set; } } 

We are having trouble figuring out how to define the indexes / mapreduces that would best support the creation of these three reports.

We tried the following map / zoom out.

 public class ViewedContentResult { public int ProductId { get; set; } public DateTime DateViewed { get; set; } public int Count { get; set; } } public class ViewedContentIndex : AbstractIndexCreationTask<ViewedContent, ViewedContentResult> { public ViewedContentIndex() { Map = docs => from doc in docs select new { doc.ProductId, DateViewed = doc.DateViewed.Date, Count = 1 }; Reduce = results => from result in results group result by result.DateViewed into agg select new { ProductId = agg.Key, Count = agg.Sum(x => x.Count) }; } } 

But this request causes an error:

 var lastSevenDays = session.Query<ViewedContent, ViewedContentIndex>() .Where( x => x.DateViewed > DateTime.UtcNow.Date.AddDays(-7) ); 

Error: "DateViewed is not indexed"

Ultimately, we want to request something like:

 var lastSevenDays = session.Query<ViewedContent, ViewedContentIndex>() .Where( x => x.DateViewed > DateTime.UtcNow.Date.AddDays(-7) ) .GroupBy( x => x.ProductId ) .OrderBy( x => x.Count ) 

This does not actually compile because OrderBy is erroneous; The graph here is not valid.

Any help here would be appreciated.

Answer 1

Each report is a different GROUP BY group if you are in SQL land, which tells you that you need three indexes: one with a month, one with weekly records, one month and one year (or maybe a little different in depending on how you are going to fulfill the request.

Now you have a DateTime that presents some problems - what you really want to do is the index component of the DateTime component, the month in the date and day component of this time. (Or just one or two of them, depending on which report you want to generate.

I am only citing your code here, so obviously it will not compile, but:

 public class ViewedContentIndex : AbstractIndexCreationTask<ViewedContent, ViewedContentResult> { public ViewedContentIndex() { Map = docs => from doc in docs select new { doc.ProductId, Day = doc.DateViewed.Day, Month = doc.DateViewed.Month, Year = doc.DateViewed.Year Count = 1 }; Reduce = results => from result in results group result by new { doc.ProductId, doc.DateViewed.Day, doc.DateViewed.Month, doc.DateViewed.Year } into agg select new { ProductId = agg.Key.ProductId, Day = agg.Key.Day, Month = agg.Key.Month, Year = agg.Key.Year Count = agg.Sum(x => x.Count) }; } 

}

I hope you see what I'm trying to achieve with this - you want ALL the components in your group, as this is what makes your grouping unique.

I can’t remember if RavenDB allows me to do this with DateTimes, and I didn’t get it on this computer, so I can’t check it, but the theory remains the same.

So for re-iteration

You need an index for your report for the week + product identifier You need an index for your report for the month + product identifier You need an index for your report for the year + product identifier

Hope this helps, sorry, I can’t give you a compilation example: the absence of a crow makes it difficult :-)