MySQL - Get most "liked" users in the past month

Question

I have a "like" system on a community forum. All internal likes are stored in a database called log_like.

CREATE TABLE `log_like` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_member` mediumint(8) NOT NULL,
  `id_message` int(10) NOT NULL,
  `id_poster` mediumint(8) NOT NULL DEFAULT '0',
  `time` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `id_member` (`id_member`),
  KEY `id_message` (`id_message`)
) ENGINE=MyISAM;

I need to get the last month most liked member ID's (id_poster field in the DDL above). So I probably need to GROUP and COUNT how many times the same "id_poster" appears in records of the past month. I want to LIMIT it to the 10 most liked. The field "time" stores the unix time for reference.

Unfortunately, I have no idea how to achieve this. Can anyone help?

Barmar · Answer 1 · 2012-12-14T20:53:09.070

1

select id_poster, count(*) like_count
from log_like
where time > unix_timestamp(date_sub(now(), interval 1 month))
group by id_poster
order by like_count desc
limit 10

It would be best if you had an index on id_poster.

edited Dec 14 '12 at 20:53

answered Dec 14 '12 at 20:16

Barmar

741,623
53
500
612

Thank you very much. However, I assume that date_sub() is based on MySQL date, and I have the time reference in Unix timestamp (seconds since epoch). Because of that, I get no results with your MySQL query. – BornKillaz Dec 14 '12 at 20:32
I've added the `unix_timestamp()` function. You couldn't figure that out by yourself, either? – Barmar Dec 14 '12 at 20:48
Yes, after my reply I have added UNIX_TIMESTAMP(), because I have the time field is in unix timestamp (seconds since epoch). However, I'm not getting the SUM of likes for each top member, instead I get integer "1" in the result set. I would like to SUM each member like, or, in other words, the number of times the top 10 id_poster is referenced in database (one row equals to one like). – BornKillaz Dec 14 '12 at 20:51
That's what `count(*)` does, it counts the number of rows. – Barmar Dec 14 '12 at 20:52
Just fixed it to add the missing `desc` in `order by`. It was showing the least liked instead of most liked. :) – Barmar Dec 14 '12 at 20:53
Please correct me if I'm wrong, but this returns the top from the last 30 days from now. Do you know how can I get only the past month likes (November 2012) instead? Thank you. – BornKillaz Dec 14 '12 at 20:59
Change the condition to: `where time between unix_timestamp('2012-11-01') and unix_timestamp('2012-12-01')`. This is all really basic stuff, you need to get out your SQL textbook or hire someone who knows what they're doing. – Barmar Dec 14 '12 at 21:03
That's it. Please notice that that was in fact part of the main question. Thank you again, really appreciated. – BornKillaz Dec 14 '12 at 21:10

score 1 · Accepted Answer · answered Dec 14 '12 at 21:37

So, here is what I ended up with:

SELECT 
  id_poster, 
  COUNT(1) AS like_count
FROM 
  log_like
WHERE 
  time BETWEEN UNIX_TIMESTAMP('2012-11-01') AND UNIX_TIMESTAMP('2012-12-01')
GROUP BY 
  id_poster
ORDER BY 
  like_count 
DESC
LIMIT 10

This way I get the past month (November 2012) top result, instead of the past 30 days from now as suggested before. (Big thanks goes to Barmar for all the work)

score 0 · Answer 3 · answered Dec 14 '12 at 21:43

0

SELECT DISTINCT id, count(*) as '#oflikes' from log_like
WHERE 
  time BETWEEN UNIX_TIMESTAMP('2012-11-01') AND UNIX_TIMESTAMP('2012-12-01')
LIMIT 10;

answered Dec 14 '12 at 21:43

Hituptony

2,740
3
22
44

MySQL - Get most "liked" users in the past month

3 Answers3