How to sort two fields with mongoose?

Question

I'm trying to allow users to see trending posts. The general idea is to sort by the most recent posts (_id: -1) and then sort those by most upvotes (upvotes_count: -1) and then limiting the results (.limit(3)). This is a bit simplified, so please ignore this implementation of "trending posts".

Unfortunately, I'm not able to return two sorts in the way that I want. So with a collection of six posts, it returns the most recent three, but it doesn't then sort them by most upvotes. For instance:

Post 6 (upvotes: 1) Post 5 (upvotes: 2) Post 4 (upvotes: 1)

I want them to be sorted like so:

Post 5 (upvotes: 2) Post 6 (upvotes: 1) Post 4 (upvotes: 1)

I'm not so interested in what happens with ties, but at a minimum, I want the posts that have more upvotes to be listed higher than those with less upvotes.

Of course, I could write a method to sort these, but surely there is a way to do this with MongoDB.

Below are some of the ways I've tried to implement this sort.

// Use sort for date and then use it again for upvotes_count
Post.find()
    .sort({_id: -1})
    .sort({upvotes_count: -1})
    .limit(3)
    .exec( function(err, posts) {
        if (err) res.send(err);
        console.log(posts);
        res.json(posts);
     });

// Use sort for date, limit the results to three, and then
// use it again for upvotes_count
Post.find()
    .sort({_id: -1})
    .limit(3)
    .sort({upvotes_count: -1})
    .exec( function(err, posts) {
        if (err) res.send(err)
        console.log(posts);
        res.json(posts);
    });

// Use sort for date and upvotes_count in one step.
Post.find()
    .sort({_id: -1, upvotes_count: -1})
    .limit(3)
    .exec( function(err, posts) {
        if (err) res.send(err);
        console.log(posts);
        res.json(posts);
     });

None have worked.

Solution 1

Refer to sort() definition.

sort({_id: -1, upvotes_count: -1})

means sort the _id firstly, then sort upvotes_count by desc order only for those same _id posts. Unfortunately, the _id is ObjectId, which is 12-byte BSON type, constructed using:

• a 4-byte value representing the seconds since the Unix epoch,
• a 3-byte machine identifier,
• a 2-byte process id, and
• a 3-byte counter, starting with a random value.

It is hard to get the same ObjectId. Namely, the _id of every record should be unique in this document. As a result, the result of your test codes are just ordered by _id desc.

Here is one example,

+---------+---------------+
| _id     |  upvote_count |
+---------+---------------+
|  1      |      5        |
|  4      |      7        |
|  3      |      9        |
|  4      |      8        |

The result of sort({_id: -1, upvotes_count: -1}) should be

+---------+---------------+
| _id     |  upvote_count |
+---------+---------------+
|  4      |      8        |
|  4      |      7        |
|  3      |      9        |
|  1      |      5        |

The upvote_count would be sorted for same _id.

However, in this case. There is on same _id in this case.

+---------+---------------+
| _id     |  upvote_count |
+---------+---------------+
|  1      |      5        |
|  4      |      7        |
|  3      |      9        |
|  2      |      8        |

The result of sort({_id: -1, upvotes_count: -1}) should be

+---------+---------------+
| _id     |  upvote_count |
+---------+---------------+
|  1      |      5        |
|  2      |      8        |
|  3      |      9        |
|  4      |      7        |

Solution 2

You can find most up voted documents for certain times

Most up voted in last 24 hours

var yesterday = Date.now()- 1000*60*60*24;

// assuming created_at contains time-stamp
find({created_at:{$gt:yesterday}}).sort({upvotes_count: -1}).limit(3)

Comments

[anon]

sort({_id: -1}) orders the items by most recent first. So once it has that list, I don't understand why it can't then sort them by upvotes_count.

[zangw]

@JonRoby, upvote_count could be sorted only for same _id, however, there is no same _id in all document, so upvote_count cannot be sorted...

[blueseal]

Refer to sort by multiple fields docs.mongodb.com/manual/reference/operator/aggregation/sort/‌​…