Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Enhancement to ActiveRecord::Base#find_by_sql to use external SQL files
Ruby
tag: v0.9.5

Fetching latest commit…

Cannot retrieve the latest commit at this time

Failed to load latest commit information.
lib
rails
test
.gitignore
MIT-LICENSE
README.markdown
Rakefile
WHATSNEW
find_by_sql_file.gemspec
init.rb
install.rb

README.markdown

Find by SQL file

This plugin extends the API of ActiveRecord::Base.find_by_sql.

A Warning

Besides the warnings on the ERB section below (don't ignore those), this code is not tested at all, and has no track record whatsoever. So there, beware.

Usage

Instead of passing the SQL statement as a string...

Elephant.find_by_sql "SELECT * FROM elephants WHERE weight='massive'"

You can pass a symbol that refers to a query file stored in RAILS_ROOT/app/queries/TABLE_NAME/SYMBOL.sql

Elephant.find_by_sql :massive_weight

Installation instructions

Via rubygems:

sudo gem install jordi-find_by_sql_file --source http://gems.github.com

# Add the following to config/environment.rb:
config.gem 'jordi-find_by_sql_file', :source => 'http://gems.github.com'

As a Rails plugin:

./script/install plugin git://github.com/jordi/find_by_sql_file.git

Motivation

The advantage of the latter approach is that the SQL file can be properly indented and commented (the indentation and comments are stripped from the logs.)

It's possible to pass named bind variables, much like in the conditions parameter of ActiveRecord::Base.find, by passing a hash as the second parameter, like so:

Elephant.find_by_sql :specifics, :color => 'grey', :weight => 6800

ERB (be careful)

It is also possible to use ERB inside the query file, but beware! Unlike the named bind variables, any data passed in via the ERB method is not properly quoted by the database adapter, leaving open the possibility of SQL injection. 99.9% of the time, you will NOT need this.

Here's an artificial (but easy to explain) example of how the (very dangerous!) ERB feature works:

Elephant.find_by_sql :single_value, :value   => 'grey',
                                    :inject! => { :field => 'color' }

The call above replaces the bind variable :value inside the SQL file, but it also populates the instance variable @field with "color", which can then be used with the usual ERB syntax, like so:

SELECT <%= @field -%> FROM elephants WHERE <%= @field -%> = :value

Legal

Copyright (c) 2008 Jordi Bunster, released under the MIT license

Something went wrong with that request. Please try again.