Node - Mongoose 3.6 - Sort query with populated field

Question

I am trying to do a query used by a remote grid, so I will have to handle sort (asc, desc) on every fields.

Here are the schemas:

var customerSchema = new mongoose.Schema({
status: {type: mongoose.Schema.Types.ObjectId, ref: 'Status'},
contact: {type: mongoose.Schema.Types.ObjectId, ref: 'Contact'}
}, { collection: 'Customer' });

customerSchema.virtual('contactName').get(function () {
   if (this.contact && this.contact.get) {
       return this.contact.get('firstName') + ' ' + this.contact.get('lastName');
   }

   return '';
});

customerSchema.virtual('statusName').get(function () {
   if (this.status && this.status.get) {
       return this.status.get('name');
   }

   return '';
});

customerSchema.set('toJSON', { virtuals: true });
customerSchema.set('toObject', { virtuals: true });
mongoose.model('Customer', customerSchema);

// STATUS
var statusSchema = new mongoose.Schema({}, { collection: 'Status' });
mongoose.model('Status', statusSchema);

// CONTACT
var contactSchema = new mongoose.Schema({
    firstName: String,
    lastName: String
}, { collection: 'Contact' });
mongoose.model('Contact', contactSchema);

And here is the query:

exports.customerList = function (predicate ,callback){
if (!predicate) predicate = 'name';
var Customers = mongoose.model( 'Customer' );
    
Customers.find()
    .select('name phone address status contact contactName statusName')
    .populate('status', 'name')
    .populate('contact', 'firstName lastName')
    .sort(predicate)
    .exec(callback);
};

The query is working when sorting on 'name' (so Customer.name) or 'address' (Customer.address) but can't get it to work when it is 'contact.firstName' (should be Customer.contact.firstName).

The fourth parameters of the populate function is an option object which can have a sort object, but doing this:

.populate('contact', 'firstName lastName', null, { sort {'firstName': 1}})

is not working (seem to sort contact list on customer).

I am completely new to mongoose (and mongo). I am trying to port a rails projects to node/express.

Is there a way I can sort my query by contact.firstName?

Edit: I ended up doing my sort manually (Array.sort) but I really don't like this solution. Sort is sync so it block node.js main thread (correct me if I'm wrong).

Is there something I don't understand? Sorting dataset is for me a database concern and not application... I was getting lot of hope about turning my rails application into node.js but it seem that some standard operation (paging a grid) are really hard to implement!

score 9 · Accepted Answer · answered Oct 18 '13 at 13:10

9

You can't sort on virtual fields or populated fields as those fields are only present in your app objects (Mongoose model instances) but the sort is executed within MongoDB.

This is one of the key limitations that results from MongoDB not supporting joins. If your data is highly relational then you should consider using a relational database instead of MongoDB.

answered Oct 18 '13 at 13:10

JohnnyHK

305,182
66
621
471

So the only way to do it in mongo style, is to put my contact within the customer collection (Customer.contact = {}) ? Is node.js and for exemple mySql has good integration? Is there good orm to handle it? – Doum Oct 21 '13 at 12:45
@Doum Right, or sort it in your own code, like you are. I haven't used MySQL with node.js, but maybe start [here](http://stackoverflow.com/questions/5818312/mysql-with-node-js). – JohnnyHK Oct 21 '13 at 13:15
2

I might be too used to work with relational DB... If i choose to embed contact within customer (customer.contact = {}), is there a way to fetch all the contacts later on without having to fetch all the customers? There is no performance issue doing it? For exemple, the customers will have a list of project, if i embed them (customer.projetcs = []) and want to list all project in my application, i will have to do like Customer.find() and fetch every single customer just to get their projects list... – Doum Oct 21 '13 at 13:48
@Doum you can do it for sure, i made an library to handle data pagination with MongoDB native drive, please take a look here for more detail [mongo-pagination](https://github.com/mrbontor/mongo-pagination) – Tobok Sitanggang Feb 26 '23 at 13:35

Node - Mongoose 3.6 - Sort query with populated field

1 Answers1

Linked