Mongoose query for "hot" posts

Question

I want to show a list of posts from the database based on likes and date, think of the basic "trending" items page.

I want to use a formula like score = likes / daysSinceCreation and then get the first 10 posts based on this score.

How can I add that sort function with mongoDB/Mongoose?

Posts.find().sort(???).limit(10).then(posts => console.log(posts));

Currently I can get top posts in last week (find if creation date larger than last week and order by score), but how can I implement a more complex sorting function without getting all the items from the DB?

eg: Today is Friday

ID  CREATION_DAY    LIKES
 1  Monday          4     // score is 5/5 = 0
 2  Tuesday         10    // score is 10/4 = 2
 3  Wednesday       3     // score is 3/3 = 1
 4  Thursday        20    // score is 20/2 = 10
 5  Friday          5     // score is 5/1 = 5

Sorted list of IDs is: [4 (Th), 5 (Fr), 2 (Tu), 3 (We), 1(Mo)]

Answer 1

This will create a new document in a "trendingposts" table:

const fiveDaysAgo = new Date(Date.now() - (5 * 24 * 60 * 60 * 1000));
const oid = new ObjectId();
const now = new Date();

Posts.aggregate([
    {
        $match: {
            createdAt: {
                $gte: fiveDaysAgo
            },
            score: {
                $gt: 0
            }
        }
    },
    {
        $project: {
            _id: true,
            createdAt: true,
            updatedAt: true,
            title: true,
            description: true,
            score: true,
            trendScore: {
                $divide: [ "$score", {$subtract: [new Date(), "$createdAt"]} ]
            }
        }
    },
    {
        $sort: {
            trendScore: -1
        }
    },
    {
        $limit: 10
    },
    {
        $group: {
            _id: { $min: oid },
            evaluatedAt: { $min: now },
            posts: { $push: "$$ROOT"}
        }
    },
    {
        $out: "trendingposts"
    }
])
    .then(...)

A few things to note:

1. If using Mongo 3.4+ the $project stage can also be written as:

   {
       $addFields: {
           trendScore: {
               $divide: [ "$score", {$subtract: [new Date(), "$createdAt"]} ]
           }
       }
   },
   

2. { $min: now } is just a hack to grab the minimum value of now on each document, even though it's the same value for all of them.

3. "$$ROOT" is the entire current document. This means your end result will be a single object with the form:

   {
       "_id" : ObjectId("5a0a2fe912a325eb331f2759"),
       "evaluatedAt" : ISODate("2017-11-13T23:51:56.051Z"),
       "posts" : [/*10 `post` documents, sorted by trendScore */]
   }
   

You can then query with:

TrendingPosts.findOne({})
    .sort({_id: -1})
    .then(trendingPost => console.log(trendingPost));

If your description/title are changing frequently, instead of $pushing the entire document in, you could just push the ids and use them for an $in query on your posts in order to guarantee the latest data.