CODEWITHSUNDEEP
mysqlselect
Fibericon
Fibericon

Reputation: 5793

Retrieving sets with highest number of combined votes among objects

I have three tables relevant to this problem - rankset, item, and vote. Rankset is essentially the category the item is placed in, such as "Favorite sport". Item is what's actually being voted on, such as "Baseball". Vote is the log of the vote itself. What I want to do is display the 25 most active ranksets on a page. Here's what the tables themselves look like:

CREATE  TABLE IF NOT EXISTS `rankset` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` TEXT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `item` (
  `id` BIGINT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(128) NOT NULL ,
  `rankset` BIGINT NOT NULL ,
  `image` VARCHAR(45) NULL ,
  `description` VARCHAR(140) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `mydb`.`vote` (
  `id` BIGINT NOT NULL AUTO_INCREMENT ,
  `value` TINYINT NOT NULL ,
  `item` BIGINT NOT NULL ,
  `user` BIGINT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB

This is what I've tried so far:

SELECT rankset.*, COALESCE(COUNT(vote.id), 0) AS votes 
   FROM rankset, item, vote 
   WHERE rankset.id = item.rankset 
   AND vote.item = item.id ORDER BY votes DESC LIMIT 25

For whatever reason, I seem to only be able to get the single most popular rankset with that. I've also tried this:

SELECT rankset.*, COALESCE(COUNT(vote.id), 0) AS votes 
   FROM rankset, vote, item 
   WHERE item.rankset = rankset.id 
   GROUP BY rankset ORDER BY votes DESC LIMIT 25

But that seems to ignore the "ORDER BY" part completely. What would be the correct way to go about this?

EDIT: Here's the fiddle: http://sqlfiddle.com/#!2/b57ac

Upvotes: 0

Views: 34

Answers (1)

Konstantin Kiselev
Konstantin Kiselev

Reputation: 171

Your queries are nearly right. In first one you should just add 'GROUP BY rankset'. Try that:

    SELECT rankset.*, COALESCE(COUNT(vote.id), 0) AS votes
      FROM rankset, item, vote 
    WHERE rankset.id = item.rankset 
      AND vote.item = item.id 
    GROUP by rankset.id 
    ORDER BY votes DESC 
    LIMIT 25;

Here's the fiddle: http://sqlfiddle.com/#!2/fe315/9.

UPDATE: The case if some rankset doesn't have any votes:

    SELECT rankset.*, COALESCE(COUNT(vote.id), 0) AS votes
       FROM rankset, item
       LEFT JOIN vote ON (vote.item = item.id)
       WHERE rankset.id = item.rankset 
       GROUP by rankset.id 
       ORDER BY votes DESC 
       LIMIT 25;

Upvotes: 2

Related Questions