NSLog();

5 Comments »

5 Responses to "SQL Help for Photo Battle Blog"

1. | Reply Patrick Burleson
   Posted 22 Feb 2008 at 11:36am #
   

   I think something like the following will do what you want:

   select
   post_id,
   ones.votes as one_vote,
   twos.votes as two_vote,
   case when ones.votes > twos.votes then 1 else 2 end as winner

   from

   (select post_id, count(*) as votes from votes where choice = 1 group by post_id) as ones join
   (select post_id, count(*) as votes from votes where choice = 2 group by post_id) as twos on
   ones.post_id = twos.post_id

   Although, that syntax might not work with MySQL. It works for Postgresql and SQL Server.

2. | Reply Patrick Burleson
   Posted 22 Feb 2008 at 11:39am #
   

   Oh, forgot the edge case of a tie. That's just another addition to the case statement returning a value to represent a tie, maybe 0.

3. | Reply Kevin Hillabolt
   Posted 22 Feb 2008 at 12:12pm #
   

   I'm guessing you either have:

   1. limit the ability to vote one time per IP/Post, or 2. have a unique index on the votes table (ip_address, post_id)?

   Either way, Patrick's solution appears to do the trick on MySQL.

   case when ones.vote = twos.vote then 1 else 0 end as tie

   1 representing true.

4. | Reply Charles Schmidt
   Posted 25 Feb 2008 at 4:04pm #
   

   I have never used MySQL, only T-SQL (microsoft sql server), so my queries might not work on your system out of the box. Stupid nonstandard language 👿

   Might I suggest setting your table up so that the choice/vote column is only ever 1 or 0. ("1" = vote for photo 1, "0" = vote for photo 2). This should allow you to build slightly faster queries by not having to search on the vote column (also lets you use BIT, but then you might have to convert() later, I'm not sure how MySQL handles bit->int conversions. At least in the tests I did locally, MS SQL won't do the conversion implictly, but I didn't see any performance drops adding an explicit conversion).

   The first stat you already have:

   select 
   	post_id,
   	sum(choice) as ones,
   	count(ip) - sum(choice) as twos
   from 
   	votes
   group by
   	post_id
   

   The second stat ("wins") you're looking for:

   select 
   	post_id,
   	sum(choice) as one_vote,
   	count(ip) - sum(choice) as two_vote,
   	case 
   		when sum(choice) > count(ip) - sum(choice) then 1
   		when sum(choice) < count(ip) - sum(choice) then 2 
   		else -1 
   	end
   from 
   	votes
   group by
   	post_id
   

   or in aggregate form:

   select 
   	sum(ones) as one_wins,
   	sum(twos) as two_wins,
   	sum(ties) as ties
   from
   (
   	select 
   		post_id,
   		case when sum(choice) > count(ip) - sum(choice) then 1 else 0 end as ones,
   		case when sum(choice) < count(ip) - sum(choice) then 1 else 0 end as twos,
   		case when sum(choice) = count(ip) - sum(choice) then 1 else 0 end as ties
   			
   	from 
   		votes
   	group by
   		post_id
   ) a
   

   Alternatively/at the very least, you should modify Patrick's logic to include cases where 1 photo receives all of the votes:

   select
   	coalesce(ones.post_id,twos.post_id) as post_id,
   	isnull(ones.votes,0) as one_vote,
   	isnull(twos.votes,0) as two_vote,
   	case 
   		when isnull(ones.votes,0) > isnull(twos.votes,0) then 1 
   		when isnull(ones.votes,0) < isnull(twos.votes,0) then 2
   		else -1
   	end
   from 
   	(select post_id, count(*) as votes from votes where choice = 1 group by post_id) as ones 
   full outer join
   	(select post_id, count(*) as votes from votes where choice = 2 group by post_id) as twos 
   on
   	ones.post_id = twos.post_id
   

5. | Reply lottadot
   Posted 25 Feb 2008 at 6:51pm #
   

   That's quite a bit to select constantly. Joins are expensive. I'd create a third table that'd hold the post_id and who won. Then create a trigger - on close of a battle it updates. (or if you don't want to mess with a trigger a cron'd script or something that kicks off a script that'll handle it.