Working with SQL in ActiveRecord
Why
I was working on a more complicated query and was completely stuck on how to construct the query. The worst part was this feeling was déjà vu, but I couldn’t remember what to search until a full day of struggling.
So this is a quick memo to myself about working with ActiveRecord and SQL statements.
First: Hint of Working in pure SQL
ActiveRecord is great, it definitely provides a nice abstraction to the database from Ruby. The first hint for me to work in pure SQL is: if I want to take advantage of SQL functionality, like having the SQL database do computations on the dataset as part of the result, build the query in SQL instead of ActiveRecord (more on why below.)
Usually anything with a join
and a clause on two different joined
columns required should be a good hint.
Second: Jumpstart SQL statement
I’m not a DBA and thanks to the ActiveRecord abstractions: also not
that familiar with working directly with the app database. To help
build the initial SQL query, use ActiveRecord’s to_sql
method to
generate a base SQL statement. Something like this:
Model_a.joins(item: :model_a_item_id).to_sql
Use this as a base statement, and layer on more SQL. :-D
Third: Use the SQL prompt!
Work from the SQL database’s prompt to develop the query. The feedback system in the SQL prompt is far superior and faster than ActiveRecord’s interface. Any errors from the database usually require a reconnection.
Fourth: Bring the SQL back
When the query is getting everything you want, bring the query back
into ActiveRecord using
ActiveRecord::Base.connection.exec_query("<SQL statement>")
, which
returns:
ActiveRecord::Result.
Methods that work on the ActiveRecrod::Result:
Method | Functionality |
---|---|
.column |
returns the column headers as an array |
.row |
returns each row of the result |
.to_hash |
returns a hash with each key being the column and |
value the value of that row | |
Enumerable | supports built in enumerable methods like: .each , .map , etc. |
Fifth: collect $200
Because you just got the database to do more work for you!
No need to deal with nil
results or empty columns. The database does
a lot of work around this and will handle empty operations properly
(dealing with computations on a nil
in Ruby is never pretty.)
Future reference:
Some important details to have as reference. (Mostly PostgreSQL hstore stuff)
joining on an hstore field | INNER JOIN X on (Y.attr -> key_id)::int == X.key_id (parenthesis are important here) |
To be updated as more stuff is discovered. :-)
ActiveRecord and Me
ActiveRecord is a great abstraction for the database, as long as
database expectations are simple, such as simple database queries
(Model.find
and Model.where
).
Once there are multiple joins and the database is expected to do more work (so Ruby doesn’t have to), ActiveRecord just doesn’t cut it for me.
Anytime there is an error with the query, ActiveRecord returns a generic error saying the database connection needs to be rest, which really means the app needs to be restarted.
Ruby vs. SQL Mindset
When working in Ruby, I definitely solve a problem very differently than using SQL. In Ruby, the mindset is to use graphs; in SQL the mindset is sets.
The end result of using Ruby or SQL will be the same, but the mental model of getting there is so different. Fundamental design of Ruby and the design of SQL are just so different.
I find it’s much better to stop working in Ruby and just be in pure SQL mode to develop a good SQL solution. (On the contrary, when have I stopped to think: how to do this in SQL? That’s something to ponder…?)
Also, there are more SQL questions than for ActiveRecord questions. 368,618 vs. 22,321, respectively, as of today (Nov 25, 2016)
Middle Ground? Squeel
One possible solution I have discovered is the Squeel gem, which looks super awesome. As much as I like writing out SQL, the context switch from Ruby to SQL and back again when reading code is quite a burden as well.
Overall
The switching context between Ruby and SQL takes a long time for me. I’ve included some pointers for me to get to working in pure SQL quicker, but after ranting a bit, I realize that learning more SQL will go a long way.
Go Forth & select * from knowledge
:-D