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 ?
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;