I have 3 tables, Users, Posts, and Likes, the schemas of them just like
Users:
id: ObjectId; nickname: string; ...
Posts:
id: ObjectId; title: string; authorId: ObjectId(relate to Users id); ...
Likes:
id: ObjectId; forId: ObjectId(relate to Posts id); ...
I'd to get the amount of all likes amount of a specific user, How can I do it? Can anyone help me, I'm using mongoose in Node.js. (I get all posts of the user and loop them then calculate the likes amount currently, but I don't think it's a good solution)
You can $lookup Posts
from Users
in an aggregation pipeline. Then, repeat $lookup again Likes
from the result. Finally, group by id of user and count the total number of likes.
db.Users.aggregate([
{
$match: {
"_id": 1
}
},
{
$lookup: {
from: "Posts",
localField: "_id",
foreignField: "authorId",
as: "post"
}
},
{
$unwind: {
path: "$post",
preserveNullAndEmptyArrays: true
}
},
{
$lookup: {
from: "Likes",
localField: "post._id",
foreignField: "forId",
as: "like"
}
},
{
$unwind: {
path: "$like",
preserveNullAndEmptyArrays: true
}
},
{
$group: {
_id: "$_id",
likeCount: {
$sum: 1
}
}
}
])
Here is a Mongo Playground