The Scoop

Over the weekend I was able to make some upgrades to Fumblerooski (the off-season being a good time for that), and to do so I took advantage of some of the new features in Django‘s development version (with the 1.2 release being just around the corner).

(Yes, I know I shouldn’t be running beta software in production, but even I have to say that Fumblerooski isn’t “mission-critical” software. Not yet, at least.)

Awhile back, Jeff Self suggested that he’d like to see head-to-head records for head coaches – how one person has fared against another. Me too, I thought. But how best to do that? In Fumblerooski’s models, it’s not a simple query, since a Coach has many CoachingJobs and CollegeCoach assignments. Plus the Game model refers to the individual College, not a specific head coach. So I added head coaches to the Game model and some utilities to populate those fields. That work continues, especially for people who were head coaches earlier in the decade but are not now. That made it easy to grab games in which two specific head coaches faced off.

But to create a list of head coaches that Mack Brown has faced since 2000, I turned to one of Django newest features: improved raw SQL querying. Much like ActiveRecord’s find_by_sql method for Rails, Model.objects.raw() makes it easy to write custom SQL when you need to, and still be able to access instantiated objects as the result.

You can select only those fields you need, deferring others, or pass parameters into the SQL. And you can add additional calculated fields, too. So when I needed to pull in a QuerySet of distinct coaches that Mack Brown had faced, plus the number of games, I could do something like this:

coach = Coach.objects.get(last_name='Brown', first_name='Mack')
coach_list = Coach.objects.raw(
"SELECT college_coach.id, college_coach.slug, count(college_game.*) as games
from college_coach inner join college_game on college_coach.id = college_game.coach2_id
where coach1_id = %s group by 1,2 order by 3 desc", [coach.id])

And thus you can see head coaching opponents for Mack Brown (or another coach). The idea isn’t new, but I really like the implementation – it’s clean and makes the mixing of Python code and SQL about as seamless as it could possibly be.