Mongoose populate and sort by length struggle

Question

I have the following mongoose schema:

const userSchema = new mongoose.Schema({
  email: { type: String, unique: true },
  fragments: [{type: mongoose.Schema.Types.ObjectId, ref: 'Fragment'}]
}, { timestamps: true, collection: 'user' });

And

const fragmentSchema = new mongoose.Schema({
  text: String,
  owner: {type: mongoose.Schema.Types.ObjectId, ref: 'User'},
}, { timestamps: true, collection: 'fragment' });

In the data, I have a reference in the Fragment, but not in the User:

User:

{
    "_id" : ObjectId("58373e571cbccb010012bfcd"),
    "email" : "email@example.com",
    // no "fragments": [ObjectId('58075ce37b7f2f01002b718f')] etc.
}

Fragment:

{
    "_id" : ObjectId("58075ce37b7f2f01002b718f"),
    "text" : "Donc, il faut changer de méthode",
    "owner" : ObjectId("58075ce27b7f2f01002b717f")
}

I would like to query users sorted by the count of number of fragments, and I can't achieve this ...

First, I'd like to make this work:

User.find({_id: '58075ce27b7f2f01002b717f'})
  .populate('fragments').exec(console.log)

returns

{ 
  _id: 58075ce27b7f2f01002b717f,
  email: 'bububg@hotmail.fr',
  fragments: []
}

while I should have at least the above fragment included.

And regarding the sorted query, here's where I am now:

User.aggregate([
  { "$project": {
    "email": 1,
    "fragments": 1,
    "nbFragments": { "$size": { "$ifNull": [ "$fragments", [] ] } }
  }},
  { "$sort": { "nbFragments": -1 } }
], console.log)

At least it runs, but all the nbFragments fields are set to 0. This might be related to the fact that .populate('fragments') doesn't work but I can't be sure.

Thanks for the help, I did not expect so much trouble using Mongodb...

---

EDIT: thanks @Veeram, unfortunately your solution is not working:

User.find({}).find({_id: '58075ce27b7f2f01002b717f'}).populate('fragments').exec(console.log)
[ { _id: 58075ce27b7f2f01002b717f,
    email: 'email@example.com',
    // no fragments entry
} ] 

while I updated my schema:

userSchema.virtual('fragments', {
  ref: 'Fragment',
  localField: '_id',
  foreignField: 'owner',
  options: { sort: { number: 1 }}, // Added sort just as an example
});

And regarding the aggregate, with:

User.aggregate([{
  $lookup: {
    from: 'Fragment',
    localField: '_id',
    foreignField: 'owner',
    as: 'fragments'
  }
}, { "$project": {
  "email": 1,
  "fragments": 1,
  "nbFragments": {
    "$size": { "$ifNull": [ "$fragments", [] ] } }
}}, { "$sort": { "nbFragments": -1 } }
]).exec(console.log)

I get:

{ 
  _id: 58075ce27b7f2f01002b717f,
  email: 'email@example.com',
  fragments: [] // fragments are always empty while they shouldn't!
}

Answer 1

Tested with following data

User:

{
    "_id" : ObjectId("58373e571cbccb010012bfcd"),
    "email" : "email@example.com"
}

Fragment:

{
    "_id" : ObjectId("58075ce37b7f2f01002b718f"),
    "text" : "Donc, il faut changer de méthode",
    "owner" : ObjectId("58373e571cbccb010012bfcd")
}

Response

[{"_id":"58373e571cbccb010012bfcd","email":"email@example.com","fragments":[{"_id":"58075ce37b7f2f01002b718f","text":"Donc, il faut changer de méthode","owner":"58373e571cbccb010012bfcd"}],"nbFragments":1}]

You define schema to use owner to populate the fragments also called virtual population. http://mongoosejs.com/docs/populate.html

const userSchema =  new mongoose.Schema({
      email: { type: String, unique: true }
    }, { timestamps: true, collection: 'user' });

var User = mongoose.model("User", userSchema);

const fragmentSchema = new mongoose.Schema({
  text: String,
  owner: {type: mongoose.Schema.Types.ObjectId, ref: 'User'},
}, { timestamps: true, collection: 'fragment' });

var Fragment = mongoose.model("Fragment", fragmentSchema);

userSchema.virtual('fragments', {
    ref: 'Fragment',
    localField: '_id',
    foreignField: 'owner',
    options: { sort: { text: -1 }}, // Added sort just as an example
});

This will now work as expected, but I don't know a way to sort on some dynamic field like count of number of fragments in mongoose. I don't think it is possible

User.find({_id: '58373e571cbccb010012bfcd'})
      .populate('fragments').exec(function (err, user) {
           console.log(JSON.stringify(user));
      });

Okay now for dynamic sorting, you have to use alternative raw mongo query with a $lookup (equivalent of populate).

const userSchema =  new mongoose.Schema({
      email: { type: String, unique: true }
    }, { timestamps: true, collection: 'user' });

var User = mongoose.model("User", userSchema);

const fragmentSchema = new mongoose.Schema({
  text: String,
  owner: {type: mongoose.Schema.Types.ObjectId, ref: 'User'},
}, { timestamps: true, collection: 'fragment' });

User.aggregate([{
  $lookup: {
    from: 'fragment',
    localField: '_id',
    foreignField: 'owner',
    as: 'fragments'
  }
}, { "$project": {
  "email": 1,
  "fragments": 1,
  "nbFragments": {
    "$size": { "$ifNull": [ "$fragments", [] ] } }
}}, { "$sort": { "nbFragments": -1 } }
]).exec(function (err, user) {
       console.log(JSON.stringify(user));
})