Search code examples
mysqldatabaseunixsocial-media-like

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


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?


Solution

  • 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)