For example, I have 3 models:
Stories:
{
title: { type: String },
text: { type: String }
}
Comments:
{
text: { type: String },
story: { type: mongoose.Schema.Types.ObjectId, ref: "Stories" }
}
Likes:
{
story: { type: mongoose.Schema.Types.ObjectId, ref: "Stories" }
}
How can I get the most popular stories, if popularity is determined by the amount of comments and likes? Story is more popular if it has more comments and likes, for example.
Thanks.
Upd: example data.
Stories:
{
"title": "First story",
"text": "This must be the MOST popular story..."
}
{
"title": "Second story",
"text": "This story is popular too, but not as the first story."
}
{
"title": "Third story",
"text": "This is a unpopular story, because dont have any comment or like"
}
Comments:
{
"title": "Foo",
"story": ObjectId("First Story ID")
}
{
"title": "Foobar",
"story": ObjectId("First Story ID")
}
{
"title": "Bar",
"story": ObjectId("Second Story ID")
}
Likes:
{ "story": ObjectId("First Story ID") }
{ "story": ObjectId("First Story ID") }
{ "story": ObjectId("First Story ID") }
{ "story": ObjectId("First Story ID") }
{ "story": ObjectId("Second Story ID") }
{ "story": ObjectId("Second Story ID") }
{ "story": ObjectId("Third Story ID") }
The result of filtering should be like this:
If you want to sort data according to popularity where popularity is defined by (total like + total comments ) then you can use this aggregate query with $lookup.
db.getCollection('stories').aggregate([
{$lookup:{from:"comments",localField:"_id", foreignField:"story", as:"comments"}},
{$lookup:{from:"likes",localField:"_id", foreignField:"story", as:"likes"}},
{ $project: { title: 1, text: 1,comments:1,likes:1, count: { $add: [ {$size: "$comments"}, {$size: "$likes"} ] } } },
{$sort:{"count":-1}}
])
for mongoose:
StoryModelName.aggregate([
{$lookup:{from:"comments",localField:"_id", foreignField:"story", as:"comments"}},
{$lookup:{from:"likes",localField:"_id", foreignField:"story", as:"likes"}},
{ $project: { title: 1, text: 1,comments:1,likes:1, count: { $add: [ {$size: "$comments"}, {$size: "$likes"} ] } } },
{$sort:{"count":-1}}
]).exec(function(err, values) {
if(err) {
// return error
}
// return values
})
OR if you want to sort by likes then comments or comments then like.
can use this query:
StoryModelName.aggregate([
{$lookup:{from:"comments",localField:"_id", foreignField:"story", as:"comments"}},
{$lookup:{from:"likes",localField:"_id", foreignField:"story", as:"likes"}},
{$group:{_id:"$_id",
totalLikes: {$sum:{$size: "$likes"}},
totalComments:{$sum:{$size: "$comments"}},
likes:{$first:"$likes"},
comments:{$first:"$comments"}
}
},
{$sort:{totalLikes:-1,totalComments:-1}} // can be comments ten like as you need
]).exec(function(err, values) {
if(err) {
// return error
}
// return values
})