Couchbase View equivalent of Select count(doc.id) from doc where doc.productId in (select productId from doc where doc.lastupdated between x and y)

Question

I'm trying to count the number of 'comments' related to a product in a couchbase bucket. That part is easy for a "full" set of data. It's just a simple map / reduce. Things get tricky when i want limit it to only products that have had changes within a date range. I can do this as two different Views in CB. One that gets the Product Id's where the dateCreated falls within my range, and then One that I pass these Id's to and it calculates my stats. The performance on this approach is horrible though. The key's for the second query aren't necessarily contiguous so i can't do a start/end on them; I'm using the .net 2.2 client for version 4.x couchbase.

I'm open to any options; i.e. Super-awesome-do-it-all-in-one-call View, or follow the 2 view approach if the client has some capacity for bulk get's against non-contiguous keys in a View (i can't find anything on this topic).

Here's my simplified example schema:

{
    "comment": {
        "key": "key1",
        "title": "yay",
        "productId": "product1",
        "dateCreated": "2016,11,30"
    },
    "comment": {
        "key": "key2",
        "title": "booo",
        "productId": "product1",
        "dateCreated": "2016,12,30"
    }
}

Answer 1

Not sure if this is what you want (also not sure about how to translate this to C#), but say you have two documents with ids comment::1 and comment::2 and a Couchbase document for each in this format.

{
   "key": "key2",
   "title": "booo",
   "productId": "product1",
   "dateCreated": "2016,12,30"
}

You can define a view (let's call it comments_by_time)

Map

function (doc, meta) {
  if (doc.dateCreated) {
    var dateParts = doc.dateCreated.split(",");
    dateParts = dateParts.map(Number);
    emit(dateParts, doc.productId);
  }
}

Reduce

_count

Then, you can use the View Query API to do a startKey and endKey range on your documents.

End point

http://<couchbase>:8092/<bucket>/_design/<view>/_view/comments_by_time

Get count of all comments

?reduce=true

{"rows":[ {"key":null,"value":2} ] }

Get documents before a date

?reduce=false&endkey=[2016,12,1]

{"total_rows":2,"rows":[
{"id":"comment::1","key":[2016,11,30],"value":"product1"}
]
}

Between dates

?reduce=false&startkey=[2016,12,1]&endkey=[2017,1,1]

{"total_rows":2,"rows":[
{"id":"comment::2","key":[2016,12,30],"value":"product1"}
]
}