Complex sort with MongoDB + Sails.js

Question

I am developing a web application using Sails.js and MongoDB. I have to query the database and sort the results by a complex function. Actually is not complex, but is more difficult than sort by a key. In this case, I have this collection

Users

{ user : "Luis", key1 : 23, key2 : 29 };
{ user : "Juan", key1 : 53, key2 : 22 };
{ user : "Paco", key1 : 26, key2 : 42 };

And I'm trying get the results order by key1 - key2

I have tried different queries having Mongo complex sorting? in account, but it didn't help me.

User.find({}).sort(function(doc1,doc2){return doc1.a . doc2.b })
 .exec(function(err,users){console.log(users)});

I have checked that this way runs ok in the Robomongo console (MongoDB GUI client).

db.eval(function() { 
    return db.scratch.find().toArray().sort(function(doc1, doc2) { 
        return doc1.a - doc2.a 
    }) 
});

But I am not able to implement this using Sails and waterline orm. I would use native connection if necessary but I don't know how to do it.

Please help me, thanks you a lot!

Do you mean sort by the different between key1 and key2? — David says Reinstate Monica
– David says Reinstate Monica, Commented Nov 25, 2015 at 21:10
yes, I mean sort by the difference between key1 and key2 — Luis González
– Luis González, Commented Nov 26, 2015 at 7:16

Sede · Accepted Answer · 2015-12-25 11:44:23Z

You need to use the .aggregate() method which provides access to the aggregation pipeline. The first stage in the pipeline is the $project stage where you use the $subtract operator to return the difference of "key1" and "key2".

The last step in the pipeline is the $sort stage where you sort your documents in ascending order using { 'diffkey': 1 } or descending order { 'diffkey': -1 }.

db.user.aggregate([
    { '$project': { 
        'user': 1, 
        'key1': 1, 
        'key2': 1, 
        'diffkeys': { '$subtract': [ '$key1', '$key2' ] }
     }}, 
    { '$sort': { 'diffkeys': 1 } }
])

Which yields:

{
        "_id" : ObjectId("567d112eaac525feef33a5b7"),
        "user" : "Juan",
        "key1" : 53,
        "key2" : 22,
        "diffkeys" : -31
}
{
        "_id" : ObjectId("567d112eaac525feef33a5b6"),
        "user" : "Luis",
        "key1" : 23,
        "key2" : 29,
        "diffkeys" : 6
}
{
        "_id" : ObjectId("567d112eaac525feef33a5b8"),
        "user" : "Paco",
        "key1" : 26,
        "key2" : 42,
        "diffkeys" : 16
}

You may be tempted to add another extra $project to the pipeline to filter out the diffkey field from your query result but doing so will cause a drop of performance. And generally speaking you shouldn't worry about one additional field in your query result.

David says Reinstate Monica · Accepted Answer · 2015-12-23 18:54:31Z

1

If you mean sort by the difference between key1 and key2 I don't think it can be done with a simple find, I think you'll need to use aggregation.

Note: I'm not familiar with Sail.js, so I'll write the query in mongoshell and let you translate to sail

db.user.aggregate([
{
    $project:{
        doc: "$$ROOT",
        sortOrder: { $subtract: ["$key1", "$key2"] }
    }
},
{
    $sort:{ sortOrder: 1}
}
]);

Not sure how exactly you want your final output to look, so you may want to add some another project at the end.

edited Dec 23, 2015 at 18:54

answered Nov 25, 2015 at 21:18

David says Reinstate Monica

20.2k22 gold badges84 silver badges124 bronze badges

2 Comments

David says Reinstate Monica Over a year ago

@luigonsec $$ROOT is a reference to the entire document being parsed. See docs on it here. Basically I didnt know what info you did or didnt want in your final output, so I just took all of it.

Volodymyr Synytskyi Over a year ago

["$key1", "key2"] - it should be ["$key1", "$key2"]

Collectives™ on Stack Overflow

Complex sort with MongoDB + Sails.js

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related