Order of results in MongoDB with $in like MySQL field('_id', ...)

Question

I'm building a quite fancy "trending" posts algorithm using MongoDB. As the algorithm consumes quite a lot of time, I'm executing my algorithm in a cron task and then I cache a sorted array of posts ids. Like this (PHP vardump):

"trending" => array("548ac5ce05ea675e468b4966", "5469c6d5039069a5030041a7", ...)

The point is that I can not find any way to retrieve them in that order using MongoDB. Using MySQL it would be done just by doing:

SELECT * FROM posts
ORDER BY FIELD(_id, '548ac5ce05ea675e468b4966', '5469c6d5039069a5030041a7',...)

What I tried so far is what it can be founde here, so now I am able to retrieve an sorted list with the weight, but not the posts. The response is like this:

{
    "result" : [ 
        {
            "_id" : ObjectId("548ac5ce05ea675e468b4966"),
            "weight" : 1
        }, 
        {
            "_id" : ObjectId("5469c6d5039069a5030041a7"),
            "weight" : 2
        }
    ], 
    "ok" : 1
}

Does anyone has achieved this or even know where to start?

Thank you!

score 1 · Answer 1 · answered Feb 20 '15 at 21:59

I'm coming from the SO post you linked. What I found was to use the $$ROOT variable provided by MongoDB.

My code looks like this:

var ids = [456, 123, 789]
  , stack = []
  ;

// Note that `i` is decremented here, not incremented like
// in the linked SO post. I think they have a typo.
for (var i = ids.length-1; i > 0; i--) {
  var rec = {
    $cond: [
      {$eq: ['$_id', ids[i - 1]]},
      i
    ]
  };

  if (stack.length === 0) {
    rec.$cond.push(i + 1);
  }
  else {
    var lval = stack.pop();
    rec.$cond.push(lval);
  }

  stack.push(rec);
}

var pipeline = [
  {$match: {
    _id: {$in: ids}
  }},
  {$project: {
    weight: stack[0],
    data: '$$ROOT' // This will give you the whole document.
  }},
  {$sort: {weight: 1}}
];

Posts.aggregate(pipeline, function (err, posts) {
  if (err) return next();

  // Use Underscore to grab the docs stored on `data` from above.
  res.json(_.pluck(posts, 'data'));
});

Note that I'm personally not exactly sure of what's going on in that for loop to build the query. :-P So most credit should go to them. Also I'm not sure yet if virtual fields (if you're using Mongoose, as I am) would be included here, but I suspect not.

Order of results in MongoDB with $in like MySQL field('_id', ...)

1 Answers1

Linked