Jason Leveille's Blog

I've had to look for this a few times in the past few days, so I figured I should post it here.

from django.db import DEFAULT_DB_ALIAS
Foo.objects.all().query.get_compiler(DEFAULT_DB_ALIAS).as_sql()

or

Foo.objects.all().query.get_compiler('default').as_sql()

Tagged in : Reference

I recently had the pleasure of presenting at the Web Technology of Frederick meetup group in Frederick, Maryland.  My topic was HTML5 and whether or not, as web developers, we should be paying attention.

Forgetting about the 30 minutes of technical difficulty at the beginning of the presentation (the projector wasn't cooperating), I thought things went very well.  Though the crowd was small (~9 attendees), they were talkative, and this was the highlight of the night for me (along with the after meetup beers at a local bar).

Read More »

Tagged in : HTML5, Presentations

Some links to complete the backstory:

• http://adactio.com/journal/1623/
• http://www.thesession.org/discussions/display/21250

Quest for the Perfect Pitch

If in fact DMCA take-down has been used for SEO gains, than I hope my small contribution to Adactio's perfect pitch quest is helpful.

So let’s get this straight. In a discussion about perfect pitch, someone mentions the website perfectpitch.com. They don’t repost any materials from the site. They don’t even link to the site. They don’t really say anything particularly disparaging. But it all takes is for the owner of perfectpitch.com to abuse the Digitial Millenium Copyright Act with a spurious complaint and just like that, Google removes the discussion from its search index.
Adactio Post 1623 - Perfect Pitch

Tagged in : DMCA, Google, Perfect Pitch, SEO

I recently had the need to add a trailing slash to http requests in a Concrete5 application (that did not have pretty urls enabled).  The issue stemed from clients adding links that did not include the trailing slash.  In the interest of keeping things clean, here are the rules I wanted to follow:

1. Only rewrite urls that contain index.php
2. Only rewrite urls where the above is satisfied and is followed by at least 1 trailing slash
3. Only rewrite urls where the above is satisfied and the url ends with at least one valid character (defined below)
4. Only rewrite urls where the above is satisfield and does not already end with a trailing slash

Read More »

Tagged in : Apache, Mod Rewrite, Regular Expressions

When he was a Junior in High School, my twin brother Jamie wrote a poem entitled, "Like All Shadows".  This morning I woke up with the poem in my head.  It has a Halloweenish sounding sound, and maybe that's why it was on my mind this morning.  Anyhow, enjoy the poem ... I know I have over the years, especially in October.

Read More »

Tagged in : Miscellaneous

I recently had the pleasure of speaking to Jeff Brown's HS Advanced Web Tools. This is the second year in a row that I've had the opportunity to visit with Jeff's students, and I have thoroughly enjoyed each visit.

Read More »

Tagged in : Presentations

BluePlate is a set of template files that we use at BlueAtlas to get up and running quickly with a site/template/application build.  We've found that by having this base set of template files, we can get from design to template completion at a much faster pace.  The other advantage comes in the form of consistancy and convention.  By having a consistent set of file names, locations, etc, we can focus our energy elsewhere.

Read More »

Tagged in : CSS, Frameworks, HTML5

This past May I graduated with a Masters Degree in Computer Science. In the ~6 months that followed, I often found myself reflecting on the value of my new degree. What follows is my best effort to get those feelings recorded.

Read More »

Tagged in : Graduate School

The Problem

A few days ago I was contacted by a colleague in need of some SQL help. He has a piece of athletic management software that tracks various metadata about teams. In his schedule table he tracks the host/opponent id and game scores, among other things. He needed an easy way to query for the number of wins, losses, and ties for each team (for all completed games). Following is the query he had constructed prior to contacting me:

SELECT id, host_id, host_score, opponent_id, opp_score,
(case
   when (host_score > opp_score) then host_id
   when (host_score < opp_score) then opponent_id
   else 0
end) as winner,
(case
   when (opp_score > host_score) then host_id
   when (opp_score < host_score) then opponent_id
   else 0
end) as loser,
(case
   when (opp_score = host_score) then opponent_id
   else 0 
end) as tie1,
(case
   when (opp_score = host_score) then host_id
   else 0
end) as tie2
FROM schedule
WHERE status = 'COM'

The query above gave him the winner and loser for each game (and whether or not the game resulted in a tie), however he needed an aggregate count of wins/losses/ties.

MySQL GROUP BY (Aggregate) Functions: SUM

I don't write SQL every day, however my instincts told me two things: 1) his solution looked much to complicated for what he was trying to accomplish (a sure sign that you might be doing something wrong), and 2) this sounded like a classic GROUP BY problem. After pulling out my trusty SQL book for reference, I constructed the following query and passed it along:

SELECT host_id, 
    SUM(host_id > opponent_id) AS wins, 
    SUM(host_id < opponent_id) AS losses, 
    SUM(host_id = opponent_id) AS ties 
FROM schedule 
WHERE status = 'COM' 
GROUP BY host_id

Conclusion

If you need aggregate data on fields in one of your database tables (AVG, COUNT, SUM, etc), your mind should immediately go to GROUP BY. It could save you time and headache.

Tagged in : MySQL