2

I need to sort the query by the average of two attributes, something like Story.scoped.order_by('(importance + points)/2').

As I saw on MongoDB and Origin documentation, this don't seems to be possible. Do I need to create a third attribute with the average result and order by it?

| Story           | points | importance
| first expected  | 1      | 1
| third expected  | 5      | 1
| second expected | 1      | 3
irio
  • 1,401
  • 14
  • 11
  • I would take a look at the following as well which discusses the use of functions to perform the evaluation: http://stackoverflow.com/questions/3212919/mongo-complex-sorting – James Wahlin Nov 12 '12 at 19:41

1 Answers1

3

You can use the aggregation framework to do this. I don't know Ruby/mongoid, but here's how you'd do it in JavaScript:

db.coll.aggregate([
    { $project: { 
        Story: 1, 
        points: 1, 
        importance: 1, 
        avg: { $divide: [{ $add: ['$points', '$importance']}, 2]}}},
    { $sort: { avg: 1}}
    ], function(err, result){
        console.log(result);
    }
);

Outputs:

[ { _id: 50a14fcb9f0d79f4de752828,
    Story: 'first expected',
    points: 1,
    importance: 1,
    avg: 1 },
  { _id: 50a14fe59f0d79f4de75282a,
    Story: 'second expected',
    points: 1,
    importance: 3,
    avg: 2 },
  { _id: 50a14fd99f0d79f4de752829,
    Story: 'third expected',
    points: 5,
    importance: 1,
    avg: 3 } ]
JohnnyHK
  • 305,182
  • 66
  • 621
  • 471
  • Works well, but why provide '1' value for Story, points and importance? It's a default value or something? – irio Nov 12 '12 at 23:38
  • @IrioMusskopf A value of `1` there just means to include the field in the projection. As you can see in the output, the actual values do come through. See docs on `$project` [here](http://docs.mongodb.org/manual/reference/aggregation/project/#_S_project). – JohnnyHK Nov 12 '12 at 23:44
  • thanks, that was just I was looking for. For those who are searching to the mongoid response, you must at least the 1.3.0 version of moped gem (currently available just through the git repository with `gem 'moped', '>= 1.3.0', git: 'git://github.com/mongoid/moped.git'`) and the code `Story.collection.aggregate([{:'$project' => { Story: 1, points: 1, importance: 1, avg: { :'$divide' => [{ :'$add' => ['$points', '$importance'] }, 2 ] } } }, { :'$sort' => { avg: 1 } } ])` – irio Nov 13 '12 at 00:22