An Ambitious ActiveRecord Adapter

I could tell you all about how awesome the internals are, or how fun it was to write, or how it’ll make you rich and famous, but instead I’m just going to show you some examples.

Get It

$ sudo gem install ambitious-activerecord

This will suck in the adapter and its dependencies (ActiveRecord & Ambition). It’s fully usable outside of Rails (I use it in a Camping app or two), as long as you’re riding ActiveRecord.

Now require it in your app:

require 'rubygems'
require 'ambition/adapters/active_record'

Examples

Basically, you write your SQL in Ruby. No, not in Ruby. As Ruby.

User.select { |u| u.city == 'San Francisco' }.each do |user|
  puts user.name
end

And that’s it.

The key is that queries aren’t actually run until the data they represent is requested. Usually this is done with what I call a kicker method. You can call them that, too.

Kicker methods are guys like detect, each, each_with_index, map, entries, to_a, and first (with no argument). Methods like select, sort_by, and first (with an argument) are not kicker methods and return a Context object without running any SQL.

Our Context object has two useful methods: to_s and to_hash. With these, we can check out what exactly we’re building. Not everyone has to_s, though. Mostly ignore these methods and treat everything like you normally would.

See, to_s:

>> User.select { |m| m.name == 'jon' }.to_s
=> "SELECT * FROM users WHERE users.name = 'jon'"

See, to_hash:

>> User.select { |m| m.name == 'jon' }.to_hash
=> { :conditions => "users.name = 'jon'" }

Equality – select { |u| u.field 'bob' }

User.select { |m| m.name == 'jon' }
"SELECT * FROM users WHERE users.name = 'jon'" 

User.select { |m| m.created_at > 2.days.ago }
"SELECT * FROM users WHERE users.created_at > '2007-09-26 20:37:47'" 

User.select { |m| m.name == 'jon' }
"SELECT * FROM users WHERE users.name = 'jon'" 

User.select { |m| m.name != 'jon' }
"SELECT * FROM users WHERE users.name <> 'jon'" 

User.select { |m| m.name == 'jon' && m.age == 21 }
"SELECT * FROM users WHERE (users.name = 'jon' AND users.age = 21)" 

User.select { |m| m.name == 'jon' || m.age == 21 }
"SELECT * FROM users WHERE (users.name = 'jon' OR users.age = 21)" 

User.select { |m| m.name == 'jon' || m.age == 21 && m.password == 'pass' }
"SELECT * FROM users WHERE 
 (users.name = 'jon' OR (users.age = 21 AND users.password = 'pass'))" 

User.select { |m| (m.name == 'jon' || m.name == 'rick') && m.age == 21 }
"SELECT * FROM users WHERE 
 ((users.name = 'jon' OR users.name = 'rick') AND users.age = 21)"

Associations – select { |u| u.field ‘bob’ && u.association.field == ‘bob@bob.com’ }

The to_s method doesn’t work on associations yet, but that’s okay: they can still query through ActiveRecord just fine.

User.select do |u| 
  u.email == 'chris@ozmm.org' && u.profile.name == 'chris wanstrath' 
end.map(&:title)

"SELECT users.id AS t0_r0, ... FROM users 
 LEFT OUTER JOIN profiles ON profiles.user_id = users.id 
 WHERE ((users.email = 'chris@ozmm.org' AND profiles.name = 'chris wanstrath'))"

Comparisons – select { |u| u.age > 21 }

User.select { |m| m.age > 21 }
"SELECT * FROM users WHERE users.age > 21" 

User.select { |m| m.age < 21 }.to_s
"SELECT * FROM users WHERE users.age < 21" 

User.select { |m| [1, 2, 3, 4].include? m.id }
"SELECT * FROM users WHERE users.id IN (1, 2, 3, 4)"

LIKE and REGEXP (RLIKE) – select { |m| m.name =~ ‘chris’ }

User.select { |m| m.name =~ 'chris' }
"SELECT * FROM users WHERE users.name LIKE 'chris'" 

User.select { |m| m.name =~ 'chri%' }
"SELECT * FROM users WHERE users.name LIKE 'chri%'" 

User.select { |m| m.name !~ 'chris' }
"SELECT * FROM users WHERE users.name NOT LIKE 'chris'" 

User.select { |m| !(m.name =~ 'chris') }
"SELECT * FROM users WHERE users.name NOT LIKE 'chris'" 

User.select { |m| m.name =~ /chris/ }
"SELECT * FROM users WHERE users.name REGEXP 'chris'"

#detect

User.detect { |m| m.name == 'chris' }
"SELECT * FROM users WHERE users.name = 'chris' LIMIT 1"

LIMITs – first, first(x), [offset, limit], [range], slice

User.select { |m| m.name == 'jon' }.first
"SELECT * FROM users WHERE users.name = 'jon' LIMIT 1" 

User.select { |m| m.name == 'jon' }.first(5)
"SELECT * FROM users WHERE users.name = 'jon' LIMIT 5" 

User.select { |m| m.name == 'jon' }[10, 20]
"SELECT * FROM users WHERE users.name = 'jon' LIMIT 10, 20" 

User.select { |m| m.name == 'jon' }[10..20]
"SELECT * FROM users WHERE users.name = 'jon' LIMIT 10, 10"

ORDER – sort_by { |u| u.field }

User.select { |m| m.name == 'jon' }.sort_by { |m| m.name }
"SELECT * FROM users WHERE users.name = 'jon' ORDER BY users.name" 

User.select { |m| m.name == 'jon' }.sort_by { |m| [ m.name,  m.age ] }
"SELECT * FROM users WHERE users.name = 'jon' ORDER BY users.name, users.age" 

User.select { |m| m.name == 'jon' }.sort_by { |m| [ m.name,  -m.age ] }
"SELECT * FROM users WHERE users.name = 'jon' 
 ORDER BY users.name, users.age DESC" 

User.select { |m| m.name == 'jon' }.sort_by { |m| [ -m.name,  -m.age ] }
"SELECT * FROM users WHERE users.name = 'jon' 
 ORDER BY users.name DESC, users.age DESC" 

User.select { |m| m.name == 'jon' }.sort_by { |m| -m.age }
"SELECT * FROM users WHERE users.name = 'jon' ORDER BY users.age DESC" 

User.select { |m| m.name == 'jon' }.sort_by { |m| -m.profiles.title }
"SELECT users.id AS t0_r0, ... FROM users 
 LEFT OUTER JOIN profiles ON profiles.user_id = users.id 
 WHERE (users.name = 'jon') ORDER BY profiles.title DESC" 

User.select { |m| m.name == 'jon' }.sort_by { rand }
"SELECT * FROM users WHERE users.name = 'jon' ORDER BY RAND()"

COUNT – select { |u| u.name == ‘jon’ }.size

User.select { |m| m.name == 'jon' }.size
"SELECT count(*) AS count_all FROM users WHERE (users.name = 'jon')" 

>> User.select { |m| m.name == 'jon' }.size
=> 21

Other Enumerables

These methods perform COUNT operations rather than loading your array into memory. They’re all kickers.

  
User.any? { |m| m.name == 'jon' }
User.all? { |m| m.name == 'jon' }
User.select { |m| m.name == 'jon' }.empty?

More Sugar

The downcase and upcase methods will map to LOWER and UPPER, respectively.

>> User.select { |m| m.name.downcase =~ 'jon%' }.to_s
=> "SELECT * FROM users WHERE LOWER(users.name) LIKE 'jon%'"

Quoting

Columns and values will be quoted using ActiveRecord’s quote_column_name and quote methods, if possible.

SELECT * FROM bugs

Found a bug? Sweet. Add it at the Lighthouse.

More information on Ambition:

- Chris Wanstrath [ chris@ozmm.org ]