I want to query for parent records (those on the one side of a one-to-many relationship) that have no children that meet a specific set of criteria. The parents in the result set may have no children at all, or they may have many children, just none that meet the criteria. And I only want distinct parents in the result set. If a parent has two children that do not meet the criteria, I only want that parent returned once.
It turns out this type of behavior comes up more often than I’d expect. Imagine a database as part of a web service that has a table of users and a table logging the “lifecycle” email updates sent to those users. (Lifecycle emails are the periodic emails a service sends out over the course of your engagement with that service—they can be purely time based, e.g. 2 weeks after sign up, or they can be criteria based, e.g. after you place your first order. But in order to avoid being spammy/annoying, you usually only want to send these once, or once every X months.) In this scenario, I would want to query for all users (these are the parents) without a record of having received a certain type of email (the children). They may have received other emails, or they may have received no emails at all, but in either case, I want them returned in the result set. Let’s build up to the solution.
As I was getting perilously close to filling up my old 256GB hard drive, I decided to treat myself to a 512GB Samsung 840 Pro SSD, which created an opportunity to upgrade to the latest Ubuntu, a task I’d neglected for two years. I usually relish Ubuntu’s semiannual releases—that is, until the Unity user interface landed.
I received an email from an editor at Fabrikzeitung, a monthly magazine published in Zurich, Switzerland by Rote Fabrik. They were planning an issue about QR Codes and asked whether I would write an article expanding upon my post, Why does that QR Code go to justinsomnia.org? Of course! As an aside: I believe this is the first article I’ve ever been commissioned to write—and got paid for.
Well, they sent me an actual physical copy of the issue, and it looks very cool.
Front cover of Fabrikzeitung #285
Here’s my article.
Code Confusion
For those who might be curious, you can read the full text here:
The last time Justinsomnia went under the knife was 2007, and the web has changed a lot since then. Allow me to impress this upon you with a single image. The graph below represents the percentage of mobile browser traffic to my blog over those last 5 years. Hello iPhone and Android users!
Mobile traffic from November 2007 to October 2012
In the span of a single year, mobile traffic jumped from almost nothing to nearly a third of my total traffic (before settling down around 15% this year). Given that there’s now a significant global audience of people browsing the web with smaller screens, I decided it was time to get up to speed on responsive web design. (If you’re in the same boat, I highly recommend checking out Apple’s developer docs on Configuring the Viewport. It was eye-opening.)
