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?
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Consider a situation that
likes.timestampis 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;