CODEWITHSUNDEEP
mongodbmongoosemongodb-querymernmongodb-atlas
Min
Min

Reputation: 528

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

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.

Upvotes: 3

Views: 1017

Answers (2)

Tom Slabbaert
Tom Slabbaert

Reputation: 22316

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?

Upvotes: 3

aarondiel
aarondiel

Reputation: 829

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)

Upvotes: 0

Related Questions