Introducing the Query Composer
Remember back in December, when I talked about introspecting block parameters? I wasn’t able to share my actual use-case at the time, because the work had been done for a client, and the client had not given permission to share it.
Well, the code is now unencumbered! I’ve released it as a gem called query-composer. Check it out on GitHub, or just install the gem:
It provides an API for programmatically generating complex SQL queries from their separate components. You specify each component and the dependencies between them, and
Query::Composer will stitch them together into the final query.
In brief, it works like this:
It can generate these queries either using derived tables (nested subqueries) or using Common Table Expressions. (Be warned, however! CTEs are still non-standard and behave differently across DBMSs. I learned the hard way that PostgreSQL in particular does no optimization across the different components of a CTE!)
It is challenging to demonstrate succinctly how useful this tool is, because if you aren’t dynamically generating significantly complex queries,
Query::Composer will actually make more work for you. And when you’re dealing with sufficient complex queries…the examples needed to demonstrate them become fairly complex, too.
Despite that, I’ve put together an example, using a hypothetical library administration system: Libraries have Books and Patrons, Books have Topics, and Patrons may borrow Books. The example, then, builds and runs a report that asks how many books each patron borrowed during some period, and compares that to the number of books each patron borrowed during some prior period. The query may be scoped by Library, and by Topic.
The example may be seen here – it creates an in-memory SQLite3 database, populates it, builds the query using the
Query::Composer, and then displays both the query, and the result.
The final query (formatted separately using sqlpp) looks like this:
The composer may be configured to use descriptive aliases instead of brief ones, but in production, your logs will thank you for saving every possible byte. :) (The system this was extracted from was generating queries that were nearly 50KB each–for the SQL alone!)
In addition to the composer, this library provides a class called
Query::Base, which is a thin wrapper
Arel::SelectManager and makes it easier to build queries with the composer. The idea is that for particularly complex query logic, you can subclass
Query::Base and encapsulate that logic there.
There’s also a class called
Query::Wrapper which lets you wrap and extend existing Arel queries (such as you might get from an ActiveRecord scope).
So, check it out and let me know what you think. Personally, I was thrilled with how block-parameter introspection simplified the dependency specification. I had also tried some other techniques, where the dependencies were explicitly (and separately) specified, but all were awkward, verbose, and made it difficult to see at a glance how a given component related to the others.
Query::Composer has significantly simplified the reporting work I’m doing for my client. I hope it can simplify yours, too!