can't sort in mongo/mongoose with populate

Question

I can't seem to sort this mongo/mongoose query. The first one returns the exact results, just not sorted in the opposite direction.

const UserSchema = new Schema({  
    username         : String,
    current          : { type: Schema.Types.ObjectId, ref: 'News' },
    friends          : [{ user: { type: Schema.Types.ObjectId, ref: 'User'}, status: String }],
    profile          : {...}

})

const NewsSchema  = new Schema({
    user       : { type: Schema.Types.ObjectId, ref: 'User' },
    owner      : { type: Schema.Types.ObjectId, ref: 'User' },
    date       : Date,
    desc       : String
});

const News = mongoose.model('News', NewsSchema);
const User = mongoose.model('User', UserSchema);

I've tried

.populate({
    path: 'friends.user',
    model: 'User',
    select: 'profile.firstname profile.lastname profile.avatar username current -_id',
    populate: {
        path: 'current',
        model: 'News',
        select: 'date owner desc',
        populate: {
            path: 'owner',
            model: 'User',
            select: 'profile.firstname profile.lastname profile.avatar username'
        },
    },
})
.sort({'current.date': -1})

as well as

.populate({
    path: 'friends.user',
    model: 'User',
    select: 'profile.firstname profile.lastname profile.avatar username current -_id',
    populate: {
        path: 'current',
            model: 'News',
            select: 'date owner desc',
            options: { sort: { date: -1 } }
        },
    })

and this one which gives an error

User.findOne({ _id: req.userId }, 'friends -_id')
.populate({
    path: 'friends.user',
    model: 'User',
    select: 'profile.firstname profile.lastname profile.avatar username current -_id',
    populate: {
        path: 'current',
        model: 'News',
        select: 'date owner desc',
    },
        options: { 
            sort: { date: -1 }
        }
    })

{
    "message": "MongooseError: Cannot populate with `sort` on path friends.user because it is a subproperty of a document array"
}

with the first and second one, it comes back as

{
    "friends": [
        {
            "_id": "5b51fd7c3f4ec33546a0664f",
            "user": {
                "profile": {
                    ...
                },
                "current": {
                    "date": "2018-07-20T15:19:00.968Z"
                }
            }
        },
        {
            "_id": "5b51fdb53f4ec33546a06655",
                "user": {
                    "profile": {
                        ...
                    },
                    "current": {
                       "date": "2018-07-20T15:19:45.102Z"
                    }
                }
            }
       ]
}

It should be reversed as "date": "2018-07-20T15:19:45.102Z" is more recent.

Is there a better more efficient way of doing the same thing with aggregate and lookup?

Users collection

 {
        "_id" : ObjectId("5b51fd2a3f4ec33546a06648"),
        "profile" : {
            "firstname" : "user1",
            "lastname" : "user1"
            "avatar" : "user1.png"
        }
        "username" : "user1",
        "friends" : [ 
            {
                "_id" : ObjectId("5b51fd7c3f4ec33546a0664f"),
                "user" : ObjectId("5b51fd643f4ec33546a0664c")
            }, 
            {
                "_id" : ObjectId("5b51fdb53f4ec33546a06655"),
                "user" : ObjectId("5b51fd903f4ec33546a06652")
            }
        ],
        "__v" : 5,
        "current" : ObjectId("5b51fd2a3f4ec33546a06649")
},
{
        "_id" : ObjectId("5b51fd643f4ec33546a0664c"),
        "profile" : {
            "firstname" : "user2",
            "lastname" : "user2"
            "avatar" : "user2.png"
        }
        "friends" : [ 
            {
                "_id" : ObjectId("5b51fdcd3f4ec33546a06658"),
                "user" : ObjectId("5b51fd2a3f4ec33546a06648")
            }
        ],
        "__v" : 5,
        "current" : ObjectId("5b51fd643f4ec33546a0664d"),
        "username" : "user2"
    },
    {
        "_id" : ObjectId("5b51fd903f4ec33546a06652"),
        "profile" : {
            "firstname" : "user3",
            "lastname" : "user3"
            "avatar" : "user3.png"
        },
        "friends" : [ 
            {
                "_id" : ObjectId("5b51fdce3f4ec33546a0665a"),
                "user" : ObjectId("5b51fd2a3f4ec33546a06648")
            }
        ],
        "__v" : 5,
        "current" : ObjectId("5b51fd903f4ec33546a06653"),
        "username" : "user3"
    }

New collection

 {
        "_id" : ObjectId("5b51fd2a3f4ec33546a06649"),
        "user" : ObjectId("5b51fd2a3f4ec33546a06648"),
        "owner" : ObjectId("5b51fd2a3f4ec33546a06648"),
        "date" : ISODate("2018-07-20T15:18:02.962Z"),
        "desc" : "user1 gibberish",

        "__v" : 0
    }



    {
        "_id" : ObjectId("5b51fd643f4ec33546a0664d"),
        "user" : ObjectId("5b51fd643f4ec33546a0664c"),
        "owner" : ObjectId("5b51fd643f4ec33546a0664c"),
        "date" : ISODate("2018-07-20T15:19:00.968Z"),
        "desc" : "user2 gibberish",
        "__v" : 0
    }


    {
        "_id" : ObjectId("5b51fd903f4ec33546a06653"),
        "user" : ObjectId("5b51fd903f4ec33546a06652"),
        "owner" : ObjectId("5b51fd903f4ec33546a06652"),
        "date" : ISODate("2018-07-20T15:19:44.102Z"),
        "desc" : "user3 gibberish",
        "__v" : 0
    }

Ashh · Answer 1 · 2018-07-20T18:48:06.203

1

You can try below aggregation

User.aggregate([
  { "$match": { "_id": mongoose.Types.ObjectId(req.userId) } },
  { "$unwind": "$friends" },
  { "$lookup": {
    "from": User.collection.name,
    "let": { "friendId": "$friends.user" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$_id", "$$friendId" ] } } },
      { "$lookup": {
        "from": News.collection.name,
        "let": { "current": "$current" },
        "pipeline": [
           { "$match": { "$expr": { "$eq": [ "$_id", "$$current" ] } } },
         ],
         "as": "current"
      }},
      { "$unwind": "$current" }
    ],
    "as": "friends.user"
  }},
  { "$unwind": "$friends.user" },
  { "$sort": { "friends.user.current.date": 1 }},
  { "$group": {
    "_id": "$_id",
    "username": { "$first": "$username" },
    "profile": { "$first": "$profile" },
    "friends": { "$push": "$friends" }
  }}
])

edited Jul 20 '18 at 18:48

answered Jul 20 '18 at 17:53

Ashh

44,693
14
105
132

it does the same thing. – totalnoob Jul 20 '18 at 18:04
It does sorting with date... Isn't it? – Ashh Jul 20 '18 at 18:05
I don't think it's sorting at all. I've updated the code and results in the post – totalnoob Jul 20 '18 at 18:12
Ok please post your all the three collections sample in the question I will explain you the `$lookup` query to do this – Ashh Jul 20 '18 at 18:17
what's User.collection.name in this context? I'm getting "MongoError: exception: Unrecognized pipeline stage name: '$lookup'" – totalnoob Jul 20 '18 at 19:02
MongoDB shell version: 2.6.10 connecting to: test > db.version() 2.6.10 – totalnoob Jul 20 '18 at 19:06
That's too low version... You have to upgrade to 3.6 or above to use `$lookup` – Ashh Jul 20 '18 at 19:06
very nice! it works for the most part. can you just explain a little bit about how it all works? also, I need owner populated through the User db like in the first example select: 'date owner desc', populate: { path: 'owner', model: 'User', select: 'profile.firstname profile.lastname profile.avatar username' }, how do I do that with your code? – totalnoob Jul 20 '18 at 22:54
You can check for the explanation here https://stackoverflow.com/questions/49953780/lookup-multiple-levels-without-unwind... You can use `$project` to limit your fields just like select of populate – Ashh Jul 21 '18 at 03:00

can't sort in mongo/mongoose with populate

1 Answers1