3

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!

Casual Coder
  • 81
  • 12
Luis González
  • 3,199
  • 26
  • 43

2 Answers2

2

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.

styvane
  • 59,869
  • 19
  • 150
  • 156
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.

David says Reinstate Monica
  • 19,209
  • 22
  • 79
  • 122