mongodb - How to order by amount of related objects in mongoose

Question

I have two models, a User model and a Post model, the Post model has a ref field that references the User model, I don't want to create an embedded Post schema within the User schema.

I want to order the Users by the amount of Posts they have, how would I do this in mongoose/and or node mongo driver.

question from:https://stackoverflow.com/questions/65908857/how-to-order-by-amount-of-related-objects-in-mongoose

Answer 1

Without and example of your schema is not easy to know what exactly you want but I think this is you need:

First of all, it will be easier if in your User schema you have an array of _id's from the total number of comments. Because in this way you won't be $lookup.

But, you can use this query:

• First $lookup to join both collections. In this first stage you create a field called post where there are the joined data.
• Then $set to replace the value for the size (i.e. how many posts has the user)-
• And last $sort to get data sorted.

db.User.aggregate([
  {
    "$lookup": {
      "from": "Post",
      "localField": "_id",
      "foreignField": "user",
      "as": "post"
    }
  },
  {
    "$set": {"post": {"$size": "$post"}}
  },
  {
    "$sort": {"post": -1}
  }
])

Example here