2

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)]

XCS
  • 27,244
  • 26
  • 101
  • 151
  • Are you looking to paginate them, or just a fixed "top 10"? The only way I can think of doing this is to use the aggregation pipeline. But if you're doing it that way, it could get heavy depending on how often it is called and how much data you have, since you'd be sorting after a calculation. I'd most likely aggregate the ids or the documents themselves into a separate "temporary" collection, and then query on that collection. It is pretty dependent on the size of your data, query/calculation/update frequencies, pagination, etc. – chrisbajorin Nov 13 '17 at 22:34
  • Well, it will probably be the homepage of the site, where trending items are displayed, think of reddit. So every user will trigger the query. I guess it could be cached and only updated once per hour or so, that could work I guess. – XCS Nov 13 '17 at 22:40
  • Can you add your `Post` schema? – chrisbajorin Nov 13 '17 at 22:44
  • It just has title, description, score and timestamps (createdAt, updatedAt). – XCS Nov 13 '17 at 22:45

1 Answers1

2

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.

chrisbajorin
  • 5,993
  • 3
  • 21
  • 34