RavenDB query with projection with parent and last child entry for certain date range

Question

Let's say I have Post and a Collection of Comments,

public class Post {
 String title;
 List<Comment> comments;
}
public class Comment {
 Date date;
 String author;
 String comment;
}

I would like to be able to know for a certain post title what is the most recent comment in a specific date range. The result show be presented as a projection with the following structure:

public class Result {
 String postTitle;
 Date commentDate
 String commentAuthor;
 String comment;
}

I'm struggling to get it work, I tried a few approaches, but could not get it right. I have an Index for this but I'm not quite sure how I can get only the last entry of the child element. I'm getting all the records in the date range and not only the last record.

This is my index:

public Posts_LastCommentDateRange() {
    map = "docs.Posts.SelectMany(post => post.comments, (post, comment) => new {" +
        "    post.title," +
        "    commentDate = comment.date," +
        "    commentAuthor = comment.author," +
        "    comment.comment" +
        "})";   
}

This is my query:

List<Result> res = session.query( Result.class, Posts_LastCommentDateRange.class )          
          .whereEquals( "title", "RavenDB Date Range" )   
          .whereBetween( "commentDate", "2019-01-02T10:27:18.7970000Z", "2019-01-25T15:01:23.8750000Z" )
          .selectFields( Result.class )
          .toList();

Any help or direction would be much appreciated.

Thanks

Why don't you loop on 'res' (the resulting records) to find the one record for which the difference between its date and 'now' is the smallest ? — Guy, Feb 24 '19 at 07:42
@Guy thanks for your reply. It's an option but my target would be to have it as directly as possible without further processing after getting the results — JohnSilver, Feb 26 '19 at 16:35

score 0 · Answer 1 · answered Mar 31 '19 at 23:44

Rather than storing one result for every post + comment, you could use the index to only output the posts latest comment with the linq Max method.

map = docs.Posts.Select(post =>
                 {
                     var latestComment = post.comments.Max(a => a.date);
                     return new {
                                  title = post.title,
                                  commentDate = latestComment.date,
                                  commentAuthor = latestComment.author,
                                  comment = latestComment.comment
                                 };
                });

So your index is basically iterating over your posts and outputting a record that holds only the latest comment. Then your query won't have to check over the comments that are definitely not the latest.

RavenDB query with projection with parent and last child entry for certain date range

1 Answers1