How to order table in mysql by likes relative to their time?

user_385264 0
Viewed 46

I have simple problem, 2 mysql tables, one holds photos and the other one likes. Structure is like this:

table `photos` - id, name, image_id
table `likes` - id_photos, timestamp

Now it's trivial to order photos by their likes and get TOP 3 like this:

 SELECT photos.*
    , COUNT(likes.id) AS likes_count 
 FROM photos 
 LEFT JOIN likes ON photos.id = likes.id_photos 
 GROUP BY photos.id 
 ORDER BY likes_count DESC LIMIT 3;

But what I want to add is time relativity, meaning, that newer likes have more "weight" and even though some photos might have more likes, their are older and they order lower than photos with fewer likes but newer ones.

Is it possible to solve this problem only in MySQL ? Or with additional processing in PHP ?

sql-order-by mysql php
1 Answers

Let consider case then likes.timestamp is UNIXTIME field, so if we use SUM(timestamp) instead count the new lakes will have more weight :

SELECT photos.*
    , COUNT(likes.id) AS likes_count 
    , SUM(likes.timestamp) AS likes_weight 
 FROM photos 
 LEFT JOIN likes ON photos.id = likes.id_photos 
 GROUP BY photos.id 
 ORDER BY likes_weight DESC LIMIT 3;

Possible to use some coefficient (for example UNIX_TIMESTAMP())

SELECT photos.*
    , COUNT(likes.id) AS likes_count 
    , SUM(likes.timestamp)/UNIX_TIMESTAMP() AS likes_weight 
 FROM photos 
 LEFT JOIN likes ON photos.id = likes.id_photos 
 GROUP BY photos.id 
 ORDER BY likes_weight DESC LIMIT 3;
user_33993560
Related