Getting total number of likes that user received by going through all his/her posts MongoDB

user_9543878 0
Viewed 605

I'm currently using MERN stack to create a simple SNS application.

However I got stuck trying to come up with a query that could go through all of the post that user posted and get the sum of likes.

Currently I've created 3 Schemas. User, Post, Reply.

User

  const userSchema = new Schema({
  
   facebookId: {
    required: true,
    type: String,
  },

  username: {
    required: true,
    type: String,
  },

  joined: Date
})

POST

const postSchema = new Schema({
  title: String,
  body: String,
  author: { type: Schema.Types.ObjectId, ref: "User" },
  datePosted: Date,
  reply: [{ type: Schema.Types.ObjectId, ref: 'Reply'}],
  tag: [ {type: String} ]

});

REPLY

 const replySchema = new Schema({
      title: String,
      body: String,
      author: { type: Schema.Types.ObjectId, ref: "User" },
      datePosted: Date,
      post: [{ type: Schema.Types.ObjectId, ref: 'Post'}],
      likes: [{ type: Schema.Types.ObjectId, ref: "User" }] // storing likes as array 
    });

As you can see I have added likes field in Reply Schema as an array that takes in User ObjectId. Say some user has posted 4 replies and each of them received 1 ,3, 4, 5 likes respectively. In the leaderboard section I want to display user info with the total number of counts that they received from all of their replies which is 1+3+4+5 = 13 likes.

Can this be achieved without adding additional fields or is there a better way of structuring the schema.

mern mongodb-atlas mongoose mongodb-query mongodb
2 Answers

If this field is going to be shown publicly then I personally recommend that instead of calculating on the fly you pre-calculate it and save it on the user as aggregating data is expensive and should not be a part of your app's logic, especially if this needs to be calculated for each user for the leaderboard feature.

With this said here is how you can calculate it with the aggregation framework

db.replies.aggregate([
    {
        $match: {
            author: userid
        }
    },
    {
        $group: {
            _id: null,
            likes: {$sum: {$size: '$likes'}}
        }
    }
]);

As I said I recommend you do this offline, run this once for each user and save a likeCount field on the user, you can then update your other routes where a like is created to update the user like count using $inc.

// .. new like created ...
db.users.updateOne({_id: liked_reply.author}, {$inc: {likeCount: 1}})

And now finding the leaderboard is super easy:

const leaders = await db.users.find({}).sort({likeCount: -1}).limit(10) //top 10?
user_107703700

you can use the models aggregate function to do that:

const userid = 1234

post.aggregate([
  { $match: { _id: userid } },
  { $lookup: {
    from: 'Reply',
    localField: 'reply',
    foreignField: '_id',
    as: 'replies'
  } },
  { $group: {
    _id: false,
    sumoflikes: { $sum: '$replies.likes' }
  } }
])

the structure works as follows:

  1. get all the posts from a user with 'userid'
  2. join the table with 'Reply'
  3. sum all of the reply.likes

(it could be that you need to throw in a $unwind: '$replies between 2 and 3 there, i am not 100% certain)

user_153782040
Related