About
rlucas.net
Blog of Randall Lucas (rlucas)

Randall Lucas
rlucas@tercent.com
Technorati

Categories

• bugfix - 17
• cheatsheets - 3
• finance - 6
• food_and_drink - 1
• frivolity - 1
• metablog - 3
• moto - 5
• poker - 2
• rants - 2
• tech_and_market_reflections - 14
• tools - 3
• vc - 10

Alternate Views

• barebones
• RSS

Lijit Search

Ruby's ActiveRecord Makes Dropping to Raw SQL a Royal Pain (Probably on Purpose)

The opinionated programmers behind Rails have generally done a good job. (There are couple of FUBARs in their bag of tricks, such as the boneheaded choice to use pluralized table names (in some places) and use automagical pluralization code to try and mediate between the singular and plural.)

There's another item I'd like to bring up, however, and that's the fact that ActiveRecord intentionally cripples your ability to do raw SQL queries. This is, I'm sure, done to discourage raw SQL hacking in favor of using the ActiveRecord objects (which, for small numbers of objects, is admittedly a superior way to do many things, because of concerns for clarity, maintainability, etc.).

However, sometimes you need SQL, dammit. Especially when you're doing a correlation between, say, different tags that describe business plans, and the people that link those business plans together, plus the number of times that such tags appear, there's just no sense in pulling thousands of records into memory, instantiating Ruby objects, and improperly reimplementing basic CS sorting algorithms to link them up. You've got all that sitting right there in your RDBMS.

ActiveRecord lets you do something like this:

BusinessPlan.find_by_sql( 
  [ 
    'SELECT s2.id FROM (COMPLICATED_SUBSELECT) AS s2', 
    var1, var2, var3 
  ]
)

Which will run the complicated SQL and replace the bind vars (question marks) in the raw SQL with var1, var2, var3, etc., and give you a bunch of BusinessPlan objects that it's instantiated off those IDs. Easy enough.

But what if you need not merely to get the objects, but to get some other important info (say, COUNT(something)) out? You're shit out of luck with ActiveRecord. The .connection.select_all method returns you an array of record hashes, but it requires fully-baked SQL (no bind vars).

• You could manually construct the SQL and manually quote each bind variable into its place, but avoiding that kind of retarded scut work is exactly why you're using Rails in the first place.
• You could try and get the DBI handle that underlies ActiveRecord (does it?), but it's very unclear as to how or if you can do that. If you call .connection.raw_connection you get a PGConn object (for PostgreSQL), not a DBI handle.
• You could open up your own new DBI handle, which involves recapitulating the Rails initialization code for ripping the config values and rewriting connection-pooling code, which is bad for all sorts of reasons, not least of which is that you're already f'ing connected to the DB!

WTF? If you read the code for the find_by_sql method, you'll see:

def find_by_sql(sql)
  connection.select_all(
    sanitize_sql(sql), 
    "#{name} Load"
  ).collect! { |record| instantiate(record) }
end

Given this, you might think: "aha, I'll just use a similar method and pass to sanitize_sql an array with my SQL and bind vars, then pass that on to select_all. No can do. sanitize_sql is a protected method.

So, here's my encapsulation-breaking, OO-unfriendly, scofflaw workaround to let you have access to what you should already get: a decent bit of code for binding SQL parameters:

(In helpers/application_helper.rb)

arb = ActiveRecord::Base
def arb.sanitize_fucking_sql(*args)
  sanitize_sql(*args)
end

Now, you can happily go about your business and, when necessary, call ActiveRecord.sanitize_fucking_sql(...) to get 'er done. No special-purpose DB connections, no wrangling thousands of objects in memory.

Caveats:

1. I suck at Ruby, I know. There's a more elegant way to add a public method to a class, but that works and I understand it.
2. Eventually, this will break. But it will probably be a long time and the, er, unique method signature I suggest should be easily globally replaced.

$Id: ruby_active_record_makes_raw_sql_a_royal_pain.txt 863 2007-05-09 18:07:04Z rlucas $

[category: /bugfix] [permalink]