Re-title: How to sort a table based on number of likes and time correlation in MySQL?

Question

I have a simple problem, there are two mysql tables, one stores photos and the other stores likes. The structure is as follows:

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

Now it is very simple to sort photos according to the number of likes and get the top 3. You can use the following query statement:

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 I want to add temporal correlation, that is, new likes have a higher "weight", even if some photos have more likes, they are older, so they are ranked lower than Photos that have fewer likes but are newer.

Is it possible to solve this problem using only MySQL? Or does it require additional processing in PHP?

Answer 1

Consider a situation that likes. timestamp is a UNIXTIME field, so if we use SUM(timestamp) instead of calculating the new number of likes, it will be given 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;

Some coefficients can be used (such as 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;