CODEWITHSUNDEEP
javascriptmongodbmongoose
Vid
Vid

Reputation: 460

MongoDB count all likes and posts for a single user using the aggregation pipeline

I need an endpoint that returns the user's info, amount of post's they've submitted and the sum of all likes their post's have received.

I'm using MongoDB for my database along with Mongoose for my Node server.

These are my models:

    var userSchema = new Schema({
        'username' : String,
        'email' : String,
        'password' : String
    });

    var photoSchema = new Schema({
        'text' : String,
        'path' : String,
        'timestamp': Number,
        'postedBy' : {type: Schema.Types.ObjectId, ref: "user"}
    });

    var likeSchema = new Schema({
        'timestamp' : Number,
        'photo' : {type: Schema.Types.ObjectId, ref: 'photo'},
        'user' : {type: Schema.Types.ObjectId, ref: 'user'}
    });

I've already accomplished this by using Javascript and multiple queries:

// Get user
let user = await userModel.findOne({_id: mongoose.Types.ObjectId(id)})
                        .select("_id, username")
                        .lean()
                        .exec();

// Get all users photos
let photos = await photoModel.find({postedBy: mongoose.Types.ObjectId(user._id)})
                        .select("_id")
                        .exec();

// Sum likes for all photos
let likes = 0;
for (let i = 0; i < photos.length; i++)
    likes += (await likeModel.find({photo: photos[i]._id}).exec()).length;

But I want to try it with the aggregate pipeline. This is my failed attempt:

let user = await userModel.aggregate(
        [
                {
                        $match: {_id: mongoose.Types.ObjectId(id)}
                }, 
                {
                        $unset: ["email", "password", "__v"]
                },
                {
                        $lookup: {
                                from: "photos",
                                localField: "_id",
                                foreignField: "postedBy",
                                as: "photos"
                        }
                }, 
                {
                        $lookup: { 
                                from: "likes",
                                localField: "photos._id",
                                foreignField: "photo",
                                as: "likes"
                        }
                },
                {
                        $unwind: "$photos"
                },
                {
                        $group: {
                                _id: "$_id",
                                username: {$first: "$username"},
                                postCount: {$sum: 1},
                                likeCount: {$sum: 1}
                        }
                }
        ]);

I don't know how to get the number of likes for each photo post. Is it possible in a single aggregation pipeline?

Would it make more sense to build it with multiple aggegation pipelines or even just with multiple queries?

Upvotes: 0

Views: 705

Answers (1)

Joe
Joe

Reputation: 28316

If you use $size to get the size of each array, you won't need the unwind or group stages.

[
    {$match: {_id: mongoose.Types.ObjectId(id)}}, 
    {$lookup: {
               from: "photos",
               localField: "_id",
               foreignField: "postedBy",
               as: "photos"
    }}, 
    {$lookup: { 
                from: "likes",
                localField: "photos._id",
                foreignField: "photo",
                as: "likes"
    }},
    {$project: {
        likeCount: {$size:"$likes"},
        postCount: {$size:"$photos"}
    }}
]

Playground

Alternately, since the only field you are getting from the user collection is _id, which you already have, you can skip the match and lookup, and just aggregate the photos collection directly:

photoModel.aggregate([
    {$match: {postedBy: mongoose.Types.ObjectId(id)}}, 
    {$lookup: { 
                from: "likes",
                localField: "_id",
                foreignField: "photo",
                as: "likes"
    }},
    {$group: {
        _id: "$postedBy",
        likeCount: {$sum:{$size:"$likes"}},
        postCount: {$sum:1}
    }}
])

Playground

Upvotes: 2

Related Questions