Indexing list count within last month

Question

I would like to be able to query the first 10 documents from a collection in RavenDB ordered by the count with a constraint in a sublist. This is my entity:

public class Post
{
    public string Title { get; set; }
    public List<Like> Likes { get; set; }
}

public class Like
{
    public DateTime Created { get; set; }
}

I've tried with the following query:

var oneMonthAgo = DateTime.Today.AddMonths(-1);
session
    .Query<Post>()
    .OrderByDescending(x => x.Likes.Count(y => y.Created > oneMonthAgo))
    .Take(10);

Raven complaints that count should be done on index time rather than query time. I've tried moving the count to a index using the following code:

public class PostsIndex : AbstractIndexCreationTask<Post>
{
    public PostsIndex()
    {
        var month = DateTime.Today.AddMonths(-1);
        Map = posts => from doc in posts
                       select
                           new
                               {
                                   doc.Title,
                                   LikeCount = doc.Likes.Count(x => x.Created > month),
                               };
    }
}

When adding this index, Raven throws a error 500.

What to do?

This actually a very hard thing to ask Raven to do. It can be done, but it would require something like this. It may be possible to change your model to make this easier. I will give it some thought and post an answer in a few days if nobody else comes up with something first. — Matt Johnson-Pint
Sounds great, Matt. I'm willing to change by model if you could give me directions to the right path? — ThomasArdal
There are a few techniques, none of them great. The "now" can't exist in the index - it has to be either passed in at time of query, or updated via reference document. Sometimes inverting the concept of "query events that happened x time ago" to "index from time of event x time forward" can work. You might need to map/reduce the likes in order to count them - or you might do some of this work client-side. There is also the clock docs concept, which can put stress on the server. It's not an easy answer... — Matt Johnson-Pint

Maurice Maurice · Accepted Answer · 2013-03-10T08:00:53

You can do this by creating a Map/Reduce index to flatten the Posts/Likes and then query over that.

The index:

public class PostLikesPerDay : AbstractIndexCreationTask<Post, PostLikesPerDay.Result>
{
    public PostLikesPerDay()
    {
        Map = posts => from post in posts
                        from like in post.Likes
                        select new Result
                        {
                            Title = post.Title,
                            Date = like.Created,
                            Likes = 1
                        };

        Reduce = results => from result in results
                            group result by new
                            {
                                result.Title, 
                                result.Date.Date
                            }
                            into grp
                            select new Result
                            {
                                Title = grp.Key.Title,
                                Date = grp.Key.Date,
                                Likes = grp.Sum(l => l.Likes)
                            };
    }

    public class Result
    {
        public string Title { get; set; }
        public DateTime Date { get; set; }
        public int Likes { get; set; }
    }
}

And the query:

using (var session = store.OpenSession())
{
    var oneMonthAgo = DateTime.Today.AddMonths(-1);
    var query = session.Query<PostLikesPerDay.Result, PostLikesPerDay>()
                        .Where(y => y.Date > oneMonthAgo)
                        .OrderByDescending(p => p.Likes)
                        .Take(10);

    foreach (var post in query)
    {
        Console.WriteLine("'{0}' has {1} likes on  {2:d}", post.Title, post.Likes, post.Date);
    }
}

Indexing list count within last month

1 Answers