Pagination using scoring algorithm


#1

Hi

I have 2 types of Documents, users and posts. Basically a user makes multiple posts. Each post records how many times somebody likes, comments on or reshares the post.

I want a simple query to get users sorted by the total number of likes, comments, reshares across all posts that user owns. Since I could have many thousands of users I need to have pagination on the result set.

Since posts and users are different docs, this was my thought as to the map func:
function (doc, meta) {
if (doc.type && doc.type == “userProfile”) {
if (doc.deleted && doc.deleted == true) {
return;
}

emit(meta.id, 0);

}

if (doc.type && doc.type == “post”) {
if (doc.parentPostId && doc.parentPostId == 0) {
if (doc.deleted && doc.deleted == true) {
return;
}

  if (doc.userProfile) {
    emit(doc.userProfile, doc.likeCount + doc.shareCount + doc.commentCount);
  }
}     

}
}

with a _sum reduce and the query using group_level 1

This does return me the users, the summed counts across their posts sorted by the summed count. My user docs will be something like ‘user::123’ and post docs ‘post::123’. Now using startkey/endkey doesn’t work with this model since that does pattern matching so pagination queries would cause the query to start in the wrong place.

Then I thought to emit a complex key where the score is included in the key, ie [doc.likeCount + doc.shareCount + doc.commentCount, doc.userProfile] but of course this wouldn’t work since this emits a row per post doc and the reduce then sums everything, so I can’t use this as a start key.

Of course I could extend the user doc and include a count attribute and each time there is an action on one of their posts I increment this count, but this seems like a huge overkill and performance impact to the db.

My question: is there a simple way that I can aggregated counts across the posts owned by a user, and use this to sort by my user docs, and how do I introduce pagination into this model?