Take the 2-minute tour ×

I fear this could get quite complicated quite quickly, but I'm wondering what the best method is of showing the latest 'activity' ordered by 'popularity' - which is measures in either 'likes' or 'views' (even perhaps both, if possible - counting likes as 2x views?).

So if I have a database full of posts with views and/or likes, I want to be able to show the top 5% most popular, posts within the last 24 hours.

I have 3 tables for this, one for posts and two for the likes and views (1 row = 1 like / view)

table 1 = "posts"

id | title | timestamp | etc.

table 2 = "posts_likes"

post_id | user_id

table 3 = "posts_views"

post_id | user_id

Using MySQL + PHP, what would be the best way of running this query to order my posts by their overall, calulated popularity?

Many thanks, Tim

share|improve this question
the best start, at least, would be to show your tables structure. –  Raphaël Althaus Feb 5 '13 at 13:23
And to show what you have tried. –  Daniel Hilgarth Feb 5 '13 at 13:24
Tim, we expect to see the table structure and what have you tried by now... –  lolol Feb 5 '13 at 13:25
I've added table structure, I've tried a few methods @daniel, but I'm struggling.. –  Tim Feb 5 '13 at 13:26
Tim, also, dont add a signature to your post. stackoverflow.com/faq#signatures –  lolol Feb 5 '13 at 13:27

3 Answers 3

up vote 1 down vote accepted

I will give you some pseudo sql just to show the idea I would use, you can add the missing bits and get it working. It assumes that the top 5% is based in the number of posts, but if it was based in the score of likes plus views, should be trivial to adjust.

SET @foo=0.05 * select count(*) from posts where (timestamp < today - 24 hours); 
PREPARE STMT FROM 'SELECT posts,sum(likes) * 2 + sum(views) as POPULAR FROM (tables joined) ORDER BY popular LIMIT ?';


share|improve this answer

I can't test this, as I don't have a MySQL server accessible right now - but the below query should get you started:

SELECT p.id, p.title, ((COUNT(l.id) * 2) + COUNT(v.id)) AS popularity 
    FROM posts p 
        LEFT JOIN posts_likes l ON l.post_id = p.id 
        LEFT JOIN posts_views v ON v.post_id = p.id 
    WHERE p.timestamp > (UNIX_TIMESTAMP()-86400)
    GROUP BY p.id
    ORDER BY popularity DESC LIMIT :return_limit

When you run the query, you'll need to pass a parameter :return_limit - Which should be 5% of the COUNT() of all posts in the past 24 hours.

share|improve this answer
I don't think hammering it to 24 hours is a good idea. A post can be popular for a very long time. –  lolol Feb 5 '13 at 20:19
But the requirements said : " I want to be able to show the top 5% most popular, posts within the last 24 hours.", so very popular old posts are irrelevant... –  pedromarce Feb 6 '13 at 14:28

The following query will do the job (I am writing it from the top of my head, so something may be wrong):

select top 5
            sum(1 / datediff(now(), dt_registration))
        from views
            id_post = posts.id_post
        group by
    ) *
        from likes
            id_post = posts.id_post
        group by
    ) *
from posts
order by
    2 desc

The idea behind is to calculate the day difference between the registration date (of each entity) and now. Then you apply the limits to infinity concept: 1/x with x getting bigger will always result a smaller number. Then we are summing all the small values and multiplying the sum of the views, likes and the post. You can also add a hammer rule, like a post needing at least n likes and at least n views (so you can prevent a post with tons of views but no likes to be popular). But this query is not scalable at all. The best idea is to create a job that will update a post column named popularity n times a day, and do all calculation you can at the time a entity is created (like adding a column where you can store a number that represents how old the entity is, like the day difference between the creation and a given date in the future, so you don't need to recalculate datediff everytime you gonna recalculate the ranking). Also, maybe you need to fine tune the idea behind limits to infinity giving it different weights. You can also turn to something like Bayesian statistics, that have been used (imdb uses it) to calculate popularity rankings (http://blog.linkibol.com/2010/05/07/how-to-build-a-popularity-algorithm-you-can-be-proud-of/).

share|improve this answer
I can't see how 5 posts can represent the 5% most popular, sure a percentage would be variable based on the number of posts, also can't see where the requirements said anything about popularity based on time from the like or view... It is an interesting reader but unrelated to the information asked, in my opinion... –  pedromarce Feb 6 '13 at 14:26
@pedromarce I think the user real problem is: how can I formulate a ranking algorithm. My post purpose is to show a few ways to do it. Top 5 is arbitrary and can be anything. But still, I don't think hammering 24 hours is a good idea. But you are right, if the user really wants exactly what he is asking for, I think your answer is right. –  lolol Feb 7 '13 at 14:09
I found your answer very interesting, the link was a nice read, just was answering a (very likely) more interesting question, but other question anyway... –  pedromarce Feb 7 '13 at 15:54
I tought the OP really problem is to formulate a tought about rankings. But that is ok, you are right, I'm gonna ask him to change the right answer. –  lolol Feb 7 '13 at 16:10

Your Answer


