I'm working on a site that maintains tournament brackets. When the bracket is created, it creates a table of match details that pairs up the players/teams. When the game is played, the score is entered and based on who wins, the row has data as to which match they go to next.
Now what I'm trying to do is get a list of brackets and find how many total matchups there are (given the cases where there's not a number of contestants that is a power of two, the seeding algorithm will give a bye to some of the contestants) and how many matchups are left to complete the bracket. The table looks like this:
so each row is a matchup on the bracket. The match number is the absolute match number (corresponding to the placement on the template used to display), and when the template is generated, it numbers the matches (for humans to read) skipping over the bye matches (if a contestant has a bye, his opponent is labeled "[bye]" and he's automatically advanced to the next match during bracket creation).
CREATE TABLE IF NOT EXISTS `match_detail` (
`bracket` int(11) NOT NULL,
`round` int(11) NOT NULL,
`match_number` int(11) NOT NULL,
`top_contestant` varchar(100) NOT NULL,
`bottom_contestant` varchar(100) NOT NULL,
`top_score` int(11) NOT NULL,
`bottom_score` int(11) NOT NULL,
`winner` varchar(100) NOT NULL,
`winner_dest` varchar(20) NOT NULL,
`loser_dest` varchar(20) NOT NULL,
`completed` datetime NOT NULL
I'm trying to figure out if there's an easy way do this in a single query, and what I've come up with is this:
Is there a more efficient way to do this? I'm not committed to the table structure, it can be changed (we're still doing POC).
SELECT bracket, SUM(IF(top_contestant='[bye]' OR bottom_contestant='[bye]', 0, 1)) AS matches, SUM(IF(winner = '', 1, 0)) AS pending FROM `match_detail` GROUP BY bracket
Thanks for any suggestions.