How I tamed a monster query using Rails ActiveRecord on top of an SQL view

Check out this gigantic query:

 

Problems:

In programming, how do we generally make overwhelming complexity understandable to human beings? We break the complexity apart, often into layers. So that what I thought I’d do here.

Here’s one part of the query that’s repetitive. It looks like it’s repeated three times with only slight variation:

Maybe we can factor out that repetition somehow? One option would have been to simply extract this part of the query into a separate function or something like that, but I decided to take this opportunity to try out an SQL view.

A view, the way I understand it, is basically a table that’s been generated via a query. For all intents and purposes, it’s a regular old table. When it comes to read operations, you can use it just like you would any other table. This means there’s no reason you can’t write Rails database migration that defines a view, define an ActiveRecord model that points at that view, and use Arel on your view.

Here’s the migration I ended up creating for my view:

This query is still a little bit yucky, but hey, it’s an improvement over the bazillion-line behemoth we started out with.

Here’s the ActiveRecord model I lay over top of my view:

Finally, here’s my report model:

Oh, and here’s the class from which NetEarnings inherits. Don’t worry about this one too much yet. I just included it for completeness.

As you can see, there’s nothing happening here that’s super involved or complicated, IMHO. The transaction_total view supplies transaction totals for services, products, and tips. The transaction_item_type_code is either SERVICE, PRODUCT or TIP, denormalized in the view for convenience in querying. That makes it easy when querying to get, conveniently and performantly, whatever value I’m after. All I have to do is use my type scope.

The funny thing is that the solution I ended up with may actually be more code than what I started with. But check out all the problems I fixed:

ActiveRecord models on top of views. Try ’em out.