Subscribe to
Posts
Comments
NSLog(); Header Image

SQL Help for Photo Battle Blog

The Photo Battle Blog keeps a simple table that contains the IP address, the vote choice ("1" or "2"), and the post_id.

I'd like to add a feature to the plugin that drives the entire process that allows me to get some statistics. I've already written up the code to generate the easy statistic: total votes for "1" vs. total votes for "2."

But I'd also like to show the "win" totals, where a single post_id is the "battle." If I win one day 40-7 but Steve wins the next day 33-32, our "win" totals would be 1-1.

So the question, then, is this: what's the best combination of MySQL and PHP to generate that information? Pulling down the entire table and parsing it via PHP may be easy on the MySQL side, of course, but leaves a lot to be desired on the PHP side. I think, though I can't be sure, that much of the calculating can be done via MySQL. Any DB wizards out there know?

P.S. Once this problem is solved, Aaron and I may clean up the plugin and offer it as donationware to others who want to run a Photo Battle Blog.

5 Responses to "SQL Help for Photo Battle Blog"

  1. 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. 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. 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. 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. 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.