August 10, 2015 Log No. 325

Yeah I couldn’t resist.

So today marked the first major subject shift we’ve had: Ruby to SQL! In some ways it was tough, because we now have to stop thinking about “methods” and “returns” and “objects” and replace that vocabulary with “clauses” and “select” and “tables”. For the most part it’s fairly straightforward (and when it’s straightforward, it’s REALLY straightforward–as my partner put it, it’s just English, almost), although more complex JOINs did prove a challenge, especially when a subquery just comes so much more naturally.

After we stared at it a while and hit upon the answer (though at that point still mostly by accident), and then stared some more, I think I have a framework now? Basically you need as many redundant JOINs as there are WHERE clauses you need to filter by. Since WHERE doesn’t get executed til after the tables/JOINs are all set, you need all the data ready to go first. Then the first WHERE filter will prune a large chunk of your data, so how will you be able to execute your second WHERE, if it happens to require that just-pruned info? You need that same data JOINed on, just with another alias. And if there’s more WHERE clauses, etc. It’s so redudant and inelegant! But it does seem to get the job done.

And this was definitely a case where it was tough to stop thinking in Ruby terms: “first get this joined table and then filter it and then look up those returned filtered values”–NO. All the filtering is at the end! Nothing gets returned until SELECT at the very end! It does taking getting used to. And even though we ultimately got that and can tell you who was the lead actor in Julie Andrews movies (Dudley Moore!) , we’re certainly not JOIN whizzes yet. The bus transfer route self-join query was a real stumper. But then again, this was just the first day!