3

In Mysql I often use the FIELD() function in the ORDER BY clause:

ORDER BY FIElD(id, '1', '6', '3', ...);

How does one get the same results in MongoDB? I tried the following:

.find(...).sort({id: [1, 6, 3]})

This did not work

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
Saif Bechan
  • 16,551
  • 23
  • 83
  • 125
  • possible duplicate of [Mongo: how to sort by external weight](http://stackoverflow.com/questions/22192098/mongo-how-to-sort-by-external-weight) – Neil Lunn Mar 24 '14 at 11:15
  • And I say that because there is a way to apply the `$cond` operator to these "values" in order to apply that "weighting" function. – Neil Lunn Mar 24 '14 at 11:17
  • 1
    You can do this with the aggregation framework as shown below however it will not use an index unlike in SQL as such this will not only be clipped in terms of RAM usage but also you will find it will get exponentially slower as you insert more. – Sammaye Mar 24 '14 at 11:37
  • Thanks for the answer, this sorting would be more of a 'nice feature' in my application rather than a necessity, maybe ill leave it out. – Saif Bechan Mar 24 '14 at 11:50
  • There is a JIRA to do this properly somewhere so it won't always be the case that you would have to use the aggregation framework, it is more of a hack than anything. – Sammaye Mar 24 '14 at 11:51
  • For the record. In the SQL engines where this syntax is valid the index is "thrown away" and simple hash matching is used. So not very efficient. – Neil Lunn Mar 24 '14 at 12:11

2 Answers2

4

We can use $indexOfArray

Console

db.collectionName.aggregate([{
    $match: {
        _id: {
            $in: [249, 244]
        }
    }
}, {
    $addFields: {
        sort: {
            $indexOfArray: [
                [249, 244], "$_id"
            ]
        }
    }
},{
    $sort: {
        sort: 1
    }
}])

PHP code

$data = $this->mongo->{$collectionName}->aggregate(
    [
        [
            '$match' => ['_id' => ['$in' => $idList]]
        ],
        [
            '$addFields' => ['sort' => ['$indexOfArray' => [$idList, '$_id']]]
        ],
        [
            '$sort' => ['sort' => 1]
        ],
        [
            '$project' => [
                'name' => 1
            ]
        ]
    ]
);
Envy
  • 510
  • 6
  • 19
3

So for the record:

Given the array [1,6,3] what you want in your query is this:

db.collection.aggregate([
   { "$project": {
       "weight": { "$cond": [
           { "$eq": ["_id": 1] },
           3, 
           { "$cond": [
               { "$eq": ["_id": 6] },
               2,
               { "$cond": [
                   { "$eq": ["_id": 3] },
                   1,
                   0
               ]},
           ]}, 
       ]}
   }},
   { "$sort": { "weight": -1 } }
])

And that gives you specific "weights" by order of your "array" of inputs to "project" weights upon the results.

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
  • That looks a lot more complex than I would expect. Will give it a go, thanks for the answer. BTW, you voted to close the question, are you sure this is the only possible solution, and will be till the end of days, lol? – Saif Bechan Mar 24 '14 at 11:46
  • @SaifBechan I am *pretty* sure that there is **only one** way to supply an external "weight" here as your "platform specific" SQL demonstrates. So **yes** I'm *pretty sure** I am speaking with some authority here after doing this for more than 26 years of my life. – Neil Lunn Mar 24 '14 at 12:00