Mongoose, sort query by populated field

Question

As far as I know, it's possible to sort populated docs with Mongoose (source).

I'm searching for a way to sort a query by one or more populated fields.

Consider this two Mongoose schemas :

var Wizard = new Schema({
    name  : { type: String }
, spells  : { [{ type: Schema.ObjectId, ref: 'Spell' }] }
});

var Spell = new Schema({
    name    : { type: String }
,   damages : { type: Number }
});

Sample JSON:

[{
    name: 'Gandalf',
    spells: [{
            name: 'Fireball',
            damages: 20
        }]
}, {
    name: 'Saruman',
    spells: [{
            name: 'Frozenball',
            damages: 10
        }]
}, {
    name: 'Radagast',
    spells: [{
            name: 'Lightball',
            damages: 15
        }]
}]

I would like to sort those wizards by their spell damages, using something like :

WizardModel
  .find({})
  .populate('spells', myfields, myconditions, { sort: [['damages', 'asc']] })
// Should return in the right order: Saruman, Radagast, Gandalf

I'm actually doing those sorts by hands after querying and would like to optimize that.

Solution 1

You can explicitly specify only required parameters of populate method:

WizardModel
  .find({})
  .populate({path: 'spells', options: { sort: [['damages', 'asc']] }})

Have a look at http://mongoosejs.com/docs/api.html#document_Document-populate Here is an example from a link above.

doc
.populate('company')
.populate({
  path: 'notes',
  match: /airline/,
  select: 'text',
  model: 'modelName'
  options: opts
}, function (err, user) {
  assert(doc._id == user._id) // the document itself is passed
})

Solution 2

Even though this is rather an old post, I'd like to share a solution through the MongoDB aggregation lookup pipeline

The important part is this:

 {
      $lookup: {
        from: 'spells',
        localField: 'spells',
        foreignField:'_id',
        as: 'spells'
      }
    },
    {
      $project: {
        _id: 1,
        name: 1,
        // project the values from damages in the spells array in a new array called damages
        damages: '$spells.damages',
        spells: {
          name: 1,
          damages: 1
        }
      }
    },
    // take the maximum damage from the damages array
    {
      $project: {
        _id: 1,
        spells: 1,
        name: 1,
        maxDamage: {$max: '$damages'}
      }
    },
    // do the sorting
    {
      $sort: {'maxDamage' : -1}
    }

Find below a complete example

'use strict';

const mongoose = require('mongoose');
const Schema = mongoose.Schema;

mongoose.connect('mongodb://localhost/lotr');

const db = mongoose.connection;

db.on('error', console.error.bind(console, 'connection error:'));
db.once('open', () => {



  let SpellSchema = new Schema({
    name    : { type: String },
    damages : { type: Number }
  });

  let Spell = mongoose.model('Spell', SpellSchema);

  let WizardSchema = new Schema({
    name: { type: String },
    spells: [{ type: Schema.Types.ObjectId, ref: 'Spell' }]
  });

  let Wizard = mongoose.model('Wizard', WizardSchema);

  let fireball = new Spell({
    name: 'Fireball',
    damages: 20
  });

  let frozenball = new Spell({
    name: 'Frozenball',
    damages: 10
  });

  let lightball = new Spell({
    name: 'Lightball',
    damages: 15
  });

  let spells = [fireball, frozenball, lightball];

  let wizards = [{
    name: 'Gandalf',
    spells:[fireball]
  }, {

    name: 'Saruman',
    spells:[frozenball]
  }, {
    name: 'Radagast',
    spells:[lightball]
  }];

  let aggregation = [
    {
      $match: {}
    },
    // find all spells in the spells collection related to wizards and fill populate into wizards.spells
    {
      $lookup: {
        from: 'spells',
        localField: 'spells',
        foreignField:'_id',
        as: 'spells'
      }
    },
    {
      $project: {
        _id: 1,
        name: 1,
        // project the values from damages in the spells array in a new array called damages
        damages: '$spells.damages',
        spells: {
          name: 1,
          damages: 1
        }
      }
    },
    // take the maximum damage from the damages array
    {
      $project: {
        _id: 1,
        spells: 1,
        name: 1,
        maxDamage: {$max: '$damages'}
      }
    },
    // do the sorting
    {
      $sort: {'maxDamage' : -1}
    }
  ];
  Spell.create(spells, (err, spells) => {
    if (err) throw(err);
    else {
      Wizard.create(wizards, (err, wizards) =>{
        if (err) throw(err);
        else {
          Wizard.aggregate(aggregation)
          .exec((err, models) => {
            if (err) throw(err);
            else {
              console.log(models[0]); // eslint-disable-line
              console.log(models[1]); // eslint-disable-line
              console.log(models[2]); // eslint-disable-line
              Wizard.remove().exec(() => {
                Spell.remove().exec(() => {
                  process.exit(0);
                });
              });
            }
          });
        }
      });
    }
  });
});

Comments

[JohnnyHK]

What version of Mongoose are you using? I know the sorting syntax changed quite a bit in 3.0.

[Adrien Schuler]

I'm using Mongoose 2.5.14. I have an important demonstration of my project in two days, so I won't take the risk to update my stack.

[mike]

Mongoose has an issue with sorting during population which has not been resolved. Keep this in mind. github.com/Automattic/mongoose/issues/2202