The correct syntax for sorting mongoose 3.x populated document

Question

I have two MongoDB collections Customer and User in 1:1 relationship. I'm trying to query both documents using Mongoose Population and sort them by User.name.

Nothing below is working. My Mongoose is 3.8.19.

Customer
    .find({})
    .populate("user", "name email phone")
    .sort({ "name": 1 })
    .exec()

Customer
    .find({})
    .populate("user", "name email phone", null, { sort: { 'name': 1 } } )
    .exec()

Customer
    .find({})
    .populate({
        path: "user",
        select: "name email phone",
        options: { sort: { "name": 1 }}
    }).
    exec()

Customer
    .find({})
    .populate({
        path: "user",
        select: "name email phone",
        options: { sort: [{ "name": 1 }]}
    })
    .exec()

I found How to sort a populated document in find request?, but no success for me.

It would be something like below in SQL:

SELECT customer.*, user.name, user.email, user.phone FROM customer 
JOIN user ON customer.user_id = user.id
ORDER BY user.name ASC

You can't sort your docs on a populated field. http://stackoverflow.com/questions/19428471/node-mongoose-3-6-sort-query-with-populated-field — JohnnyHK, Dec 05 '14 at 15:10

score 8 · Accepted Answer · edited May 23 '17 at 12:21

Thanks to @JohnnyHK in comment, it is not possible to sort the populated fields in MongoDB and Moongose. The only option is to sort the entire array of results manually.

Mongo has no joins. The only way to sort populated documents is todo it manually after you receive all results.

I solved this by using Array.sort([compareFunction]) to sort the output resulted from Mongoose query. However, it could be a big impact on performance when sorting a large set of data.

As a side node, I'm considering to move to a relational database with node.js.

score 1 · Answer 2 · edited Aug 01 '15 at 04:18

1

I don't know why people are saying you can't sort a populated field....

model.findOne({name: request.params.posts})
    .populate('messages', '_id message createDate', null, { sort: { 'createDate': -1 } })
    .exec(function(error, results) {
})

Where my model "posts", has a ObjectID messages array in it, that is populated here and sorted by it's createDate. It works great.

edited Aug 01 '15 at 04:18

Sithu

4,752
9
64
110

answered Aug 01 '15 at 02:28

Trihedron

246
2
14

It could probably sort messages by each post, not by all entire result. Your solution is quite similar to my second example which couldn't sort the entire result by populated document. – Sithu Aug 01 '15 at 03:41
I am not sure why this was voted down. This answer works. Don't forget to add null or it won't work. – Eray T Dec 10 '19 at 18:51

The correct syntax for sorting mongoose 3.x populated document

2 Answers2

Linked