Score entity based on its likes count and creation time

Question

When reading from database, I want to sort my Post entities based on two factors:

• likes count (the more the better)
• age (the newer the better)

Currently I have implemented it this way (as a calculated value):

@Entity
public class Post {

    // divide timestamp by a day length so after each day score decrements by 1
    @Formula("UNIX_TIMESTAMP(creation_date_time) / 24 * 60 * 60 * 1000 + likes_count")
    private long score;

    @CreationTimestamp
    private LocalDateTime creationDateTime;

    @Min(0)
    private long likesCount;
}

It works fine but may not be the best approach because:

1. I think the RDBMS cannot make any index for score attribute.
2. The hard-coded function UNIX_TIMESTAMP() is specific to MySQL. So this will cause problems if I want to use another database (say H2) in my test environment.

Answer 1

I think of a solution that could be interesting and could help you maintain your score updated. Would be to create a scheduler that will be, every certain amount of time (for my example it will be done daily at 1 am), will go through all posts updating their scores, that way maintaining an updated score.

@Component
public class Scheduler {

    @Autowired
    PostService postService;

    // Dialy routine that will start at 1:00 am.
    @Scheduled(cron="0 0 1 * * *")
    public void updateDateScore() {

        // Of course, I wouldn't recommend doing this all at once.
        // I would do it in batches, but this is just to give you an idea.
        List<Post> posts = postService.getAll();
        for(Post p: posts) {
           LocalDateTime time = p.getCreationTime();
           ZoneId zoneId = ZoneId.systemDefault(); 
           long epoch = time.atZone(zoneId).toEpochSecond();
           // Your formula.
           long score = epoch / 24 * 60 * 60 * 1000 + p.getLikesCount();
           p.setScore(score);
           postService.update(p);
        }

    }

}

In order for scheduled tasks to work, you must add the following annotation to your Main class, @EnableScheduling. Of course, this will work on all RDBMS, so you won't need to worry about what DB you are using and you would have an updated index at all times.

Recommendations

• This should be done in batches, that way it would perform a lot better.
• I would of course paginate my getPost() method, so that I would only fetch a plausible amount to update each loop.
• Also, I would set a maximum date to fetch posts. Anyways, after a certain amount of time a post might not be so relevant.

Answer 2

Use database triggers to update/maintain those side aggregate tables. Running heavy scheduled jobs for such things (which cause load spikes) really wouldn't make sense...

Also, below WHERE clause will never use indexes. Never ever.

UNIX_TIMESTAMP(creation_date_time) / 24 * 60 * 60 * 1000 + likes_count