Schema/Strategy and queries for ranking system

I've got the following schema in my db:

| items | CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8_unicode_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

| rankings | CREATE TABLE `rankings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `rank` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

I basically want to users to be able to add items and rank them against each other. Administrators in my system would be able to v开发者_如何学Pythoniew a list of items sorted by the average rank. I'm currently determining the average rank across all users with a query like this:

SELECT AVG(r.rank) AS rank, i.* FROM rankings r LEFT OUTER JOIN items i ON r.item_id = i.id GROUP BY i.id ORDER BY rank ASC;

What I'm struggling with is how to craft a query or have an insert strategy when a new item is added and has not been ranked by any users yet. The two viable strategies I see are:

1) Construct the query to get the sorted list across all users such that unranked items are still accounted for but perhaps added to the end of the list for each user sorted by created date

2) When a new item is added, add a row in the rankings table for each user for the new item, placing it last in the current rankings

Thoughts?

---

I'm not a mysql user, so one of the two syntaxes will work for you. Use ifNull in your AVG calculation.

SELECT AVG(ifNull(r.rank,0)) AS rank, i.* 
  FROM rankings r LEFT OUTER JOIN items i ON r.item_id = i.id
GROUP BY i.id
ORDER BY rank ASC;

Or, if mysql doesn't allow the nesting of functions like that:

SELECT AVG(s.rank) as avg_rank, s.*
  FROM ( SELECT ifNull(r.rank,0) AS rank, i.* 
           FROM rankings r LEFT OUTER JOIN items i ON r.item_id = i.id
         GROUP BY i.id ) as s
ORDER BY avg_rank ASC;

You should also probably put in a foreign key relationship between rankings.item_id and items.id in your table definitions with an ON DELETE CASCADE.

---

I ended up going with option #2 above for now, which is to create a rankings row for each user/item combination when a new item is added. I'm worried about scalability but it's enough to get going anyway.