Mongoose populate count

Question

I want to know how many users are associated to my office and for this I use the count option for virtual schema.

Office schema:

const OfficeSchema = mongoose.Schema({
  name: { type: String },
  admin: { type: mongoose.Schema.Types.ObjectId, ref: 'User' },
}, { toJSON: { virtuals: true } });

User schema:

const UserSchema = mongoose.Schema({
  email: { type: String, unique: true, required: true },
  first_name: { type: String },
  last_name: { type: String },
  office: { type: mongoose.Schema.Types.ObjectId, ref: 'Office' },
}, { toJSON: { virtuals: true } });

Office-user relationship:

OfficeSchema.virtual('usersCount', {
  ref: 'User',
  localField: '_id',
  foreignField: 'office',
  justOne: false,
  count: true
});

When I add a query argument to the find method it works fine, I get the users count for a specific office.

Office.find({ admin: userId }).populate([
    { path: 'admin', select: '_id email first_name last_name' },
    { path: 'usersCount', select: '_id' }
  ]);

But when there is no query object (meaning get all offices and their users count) I get the same number of users for every office, the total number. I just want to get the associated number of users for each office. Is this possible?

Answer 1

Behind the scenes Mongoose is going to do a query for all users which match any of the office ids found, as you are probably aware, and that is why you are getting the same count across the board.

But because Mongoose is doing this as a separate query, nothing is to stop you from performing the same yourself, in a post-hook. See https://mongoosejs.com/docs/middleware.html#post-async

Thus, upon returning all offices, you then need to fetch all users, perform a group (See group) on them by their office, & count the number in each resulting group (See count).

By this point, you may want to consider performing the same as an aggregate pipeline, which can be performed more efficiently on the server-side. See this on writing a group pipeline for the aggregate model method: https://docs.mongodb.com/manual/reference/operator/aggregation/group/