How to get a "top20" with mongoose queries?

Question

How to get a "top20" with mongoose queries?

I have a mongoDB database which log web video view:

S_logs =  new mongoose.Schema({
        video_id :  String,
        time : Number           //timestamp of when the video was saw
});
M_logs = mongoose.model('logs', S_logs);

I need to get the dayly top20 of most viewed video, I am able to get it using aggregate:

M_logs.aggregate([
            {
                "$match" : {
                    "time" : {
                        "$gt" : time_lower_bound,
                        "$lt" : time_upper_bound
                    }
                }
            },
            {
                "$group" : {
                    "_id" : "$video_id",
                    "count" : { "$sum" : 1 }
                }
            },
            {
                "$sort" : { "count" : -1 }
            }
        ]).limit(20);
    }

Which work, but for consistency sake (it would be the only place in my code where I use aggregate instead of queries), I d like to use queries for this too, I came up with:

M_logs[from] .find()
             .select('video_id')  //Get all video_id
             .where('time')       //in the selected timespan
             .gt(time_lower_bound)
             .lt(time_upper_bound)
             .sort('video_id')    //sort them
             .distinct('video_id')//get only the video_id list
             .limit(20);          //get only the first 20 to appear

But I get:

Error: field selection and slice cannot be used with distinct

I thought I could use count some way or another, but it just return the total of document returned by the query.

To me it seems that if I want to do this with query, I have the choice between making 20 queries, or one which will return all document, then group and count myself.

Am I missing some function/trick/whatever that would allow me to return the count of document with same video_id, sorted by descending order?

node.js
mongodb
mongoose

Answer 1

You can't aggregate your data using query.

If you want to do it with basic query, you should store this information explicitly (e.g. maintain views counter).

If all you want is coding-style consistency, then you may rewrite your aggregation query with mongoose aggregation dsl:

M_logs.aggregate().match({
  time: {
    $gt: time_lower_bound,
    $lt: time_upper_bound
  }
}).group({
  _id: "$video_id",
  count : { $sum: 1 }
}).sort({
  count: -1
}).limit(20)

Though, it'll produce the same aggregation query.