Err the Blog Atom Feed Icon
Err the Blog
Rubyisms and Railities
  • “Full of Ambition”
    – Chris on August 30, 2007

    This idea has been kicked around a whole lot, I know. In IRC. Dark alleyways. Sweaty dance halls. Courts of law. It’s this: instead of writing SQL, write Ruby. Generate the SQL behind the curtain.

    Something like this:

    User.detect { |u| u.name == 'Jericho' && u.age == 22 }
    Or this: 
    User.select { |u| [1, 2, 3, 4].include? u.id }
    Or even this: 
    User.select { |u| u.name =~ 'rick' }.sort_by(&:age)

    It’d be cool, right?

    Don’t get me wrong, we’re already able to express our queries in Ruby. There’s ez-where and Squirrel, and probably a ton more. But those are DSLs for querying with Ruby, not as Ruby. I want to use the straight-up Enumerable I know and love, nothin’ else. Call me old fashioned.

    An Ambitious Undertaking

    Erlang’s Mnesia database is something like what I want: you write your queries in plain Erlang and they are translated into Mnesia-queries by walking the parse tree. Nice trick, but listen up: Ruby has a parse tree, too, and we can get at it pretty easily thanks to ParseTree.

    So, we do. Introducing Ambition.

    $ sudo gem install ambition -y

    Play with it in your Rails console using the ActiveRecord logging hack:

    $ script/console
>> ActiveRecord::Base.logger = Logger.new(STDOUT)
=> #<Logger:0x2814134 ...>
>> require 'ambition'
=> []

    Some examples using an ActiveRecord model, followed by the SQL executed in the background:

    User.first
"SELECT * FROM users LIMIT 1"

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

User.select { |u| u.email =~ /chris/ }.first
"SELECT * FROM users WHERE (users.`email` REGEXP 'chris') LIMIT 1"

User.select { |u| u.karma > 20 }.sort_by(&:karma).first(5)
"SELECT * FROM users WHERE (users.`karma` > 20)
 ORDER BY users.karma LIMIT 5"

User.select { |u| u.email =~ 'ch%' }.size
"SELECT count(*) AS count_all FROM users
 WHERE (users.`email` LIKE 'ch%')"

User.sort_by { |u| [ u.email, -u.created_at ] }
"SELECT * FROM users ORDER BY users.email, users.created_at DESC"

User.detect { |u| u.email =~ 'chris%' && u.profile.blog == 'Err' }
"SELECT users.`id` AS t0_r0 ... FROM users
 LEFT OUTER JOIN profiles ON profiles.user_id = users.id
 WHERE ((users.`email` LIKE 'chris%' AND profiles.blog = 'Err'))
 LIMIT 1"

    And so forth. A big list of examples can be found in the README.

    Kicking Around Data

    A good thing to keep in mind 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, and first (with no argument). Methods like select, sort_by, and first (with an argument) are not kicker methods and return a Query object without running any SQL.

    As such, you can garner some information from a Query object:

    >> user = User.select { |u| u.name == 'Dio' }
=> (Query object: call #to_sql or #to_hash to inspect...)
>> user.to_sql
=> "SELECT * FROM users WHERE users.`name` = 'Dio'"
>> user.to_hash
=> {:conditions=>"users.`name` = 'Dio'"}
>> user.first  # => SQL is run
=> #<User:0x36896e4 ...>

    Note the to_hash—Ambition doesn’t actually run any SQL, it just hands this hash to ActiveRecord::Base#find.

    Anyway, kickers have useful implications for Rails apps. Take this controller:

    class BandsController < ApplicationController
  def index
    @bands = Band.sort_by(&:name)
  end
end

    Since no kicker method is called, @bands is just a Query object—no SQL run. The SQL is only run once we call each in our view:

    <h1>Rocktastic Bands<h1>
<ul>
<% @bands.each do |band| %>
  <li><%= band %></li>
<% end %>
</ul>

    Now, let’s say you grow a bit and want to a) fragment cache and b) reduce queries. Standard stuff.

    Two birds, one stone:

    <h1>Rocktastic Bands<h1>
<% cache do %>
  <ul>
  <% @bands.each do |band| %>
    <li><%= band %></li>
  <% end %>
  </ul>
<% end %>

    If Rails finds a cached fragment, the SQL is never run. Slick.

    The Catch

    This is pretty new, so watch the sharp edges. While we aren’t good at executing arbitrary Ruby inside the block, we can handle variables.

    Practically speaking, instead of writing: 
    User.select { |u| u.created_at = 2.days.ago }.first
    Write: 
    date = 2.days.ago
User.select { |u| u.created_at = date }.first

    Instance variables and simple method calls work fine, too. Expect full Ruby support in a future release. (Expect it sooner if someone sends in a patch!)

    Big Dreams

    Ideally, this thing could turn into something like Rack for databases. Query DataMapper, Sequel, or ActiveRecord using Ruby’s plain jane Enumerable API. Hey, maybe we can thrown an OODB or two into the mix?

    The usual suspects:

    • Report feature requests, shortcomings, & bugs at Lighthouse.
    • SVN’s at svn://errtheblog.com/svn/projects/ambition
    • Code at http://projects.require.errtheblog.com/browser/ambition
    • RDoc at Rock

    I’ve been running this on Cheat for a few days and it’s going real swell. Check the source for awesomeness like @sheets = Sheet.sort_by(&:title).

    Watch this space as we grow up our little ambition.

    Update: Oh yeah, KirinDave came up with the name Ambition. Thanks.

    Update 2: Okay, added some stuff tonight: You can now do cross-table sort_bys: 
    User.sort_by { |u| [ u.profile.name, u.ideas.karma ] }

    That didn’t work before. Doh. I also added query support for any?, all?, and empty?—they do a COUNT behind the scenes, so feel free to give them crazy conditions. All three are kickers.

    Also added were the entries and to_a kickers. User.to_a is the same same as User.find(:all). Works as an all-purpose kicker, too—User.select { |u| u.name == ‘kicker’ }.to_a and whatnot.

    Finally, slice is now an alias for []. You guys can thank PJ for that one.

    The new gem is out and it’s hot. I added some empty specs for destructive and constructive methods—dangerous thinking, I know. We’ll see.

  • monde, about 2 hours later:

    awesome, writing apps will be even faster still

  • Ryan Allen, about 2 hours later:

    Holy crap crap crapola, that is f’ing awesome.

  • Hugh, about 2 hours later:

    ... wow … very sweet

  • Daniel Lucraft, about 2 hours later:

    You should get together: http://rubyurl.com/Eal

  • Fabien, about 3 hours later:

    As a co-developer of ez_where, I must admit I really like Ambition. Especially the kicker/non-kicker methods to allow for lazy evaluation. Ez_where was a nice toy project to play around with, I guess… Once Ambition is ready for production use I will definately consider this instead.

  • jason, about 4 hours later:

    Excellent, can you make it stable for production immediately. Thanks.

  • RainChen, about 4 hours later:

    “Two birds, one stone”... ????

    Awesome.What a fxxking talent you have! ??TMD??

  • Pratik, about 4 hours later:

    You’re nuts dude..you’re changing the way we think!!

  • PJ, about 5 hours later:

    Taught him everything he knows. It’s hard watching them grow up so quickly.

  • bryanl, about 5 hours later:

    wow.. this is awesome. it’s 6:29, i’m still in bed and playing with ambition in a console thinking up all sorts of cool possibilities.

  • Mario Aquino, about 5 hours later:

    I think this is really awesome. In the ‘sort_by’ clauses above, you are passing in &:name instead of just the symbol. Why?

  • PJ, about 5 hours later:

    Mario, sort_by takes a block and &:name is just shorthand for { |u| u.name }

  • kadoudal, about 6 hours later:

    I wake up this morning and there it is….. shoudl I wait for Xmas to get a production version ? any feedback about performance ? (blocks + SQL vs SQL )

  • RaVbaker, about 7 hours later:

    I’ve something like this in PHP.

    It’s my example of SELECT:

    DB::SELECT(array(
    "FROM" => "menus",
    "WHERE"=>array(
        'id_menu NOT IN  (SELECT id_parent FROM menus) '=> '',
        'id_parent = %d' => 0
    ),
    "ORDER BY"=>array(
        'priority' => 'DESC',
        'id_parent' => 'ASC',
        'id_menu' => 'ASC',
    ),'LIMIT' => 4
));
  • Gustav Paul, about 7 hours later:

    wow dude, this is … profound

  • Miha Filej, about 8 hours later:

    ambition + map_by_method = User.sort_by_age

    Yay! :D

  • Jon Yurek, about 8 hours later:

    Hah! I was just having this exact discussion the other day with a coworker about the future of Squirrel. Actually, it’s even weirder, since the only things you’re not doing that we weren’t discussing are conditionals and non-AR accessors inside the blocks.

    This really looks awesome. Great job!

  • mike, about 8 hours later:

    This is unbelievable. It’s so wonderful working with a technology that has such forward thinking individuals improving and evolving it. Thank you! I can’t wait until this is production ready!

  • Matthew King, about 9 hours later:

    Either I’m just ignorant, or Ambition currently assumes the database is MySQL. Perhaps both?

  • dustym, about 9 hours later:

    Very hot.

  • josh, about 9 hours later:

    Very nifty. I can see some potential. Looks like all your joining is LEFT OUTER, which makes sense for eager loading, but can be onerous for cases where you just want the data from the one model and only query by data in the associated models. Are queries that use INNER JOIN on the roadmap?

  • Chris Carter, about 9 hours later:

    You didn’t use the image… Actually, correct me if I am wrong, but this post has no image….

  • Jay Phillips, about 9 hours later:

    Hehe, this looks like it’d be compatible with the Methodphitamine too.

    User.select(&its.name =~ ‘rick’).sort_by &its.age

  • slainer68, about 9 hours later:

    awesome ! this kind of new things really only happens inside the rails community.

  • Zack, about 10 hours later:

    Wow – very sweet stuff Chris.

    Also, check the rdoc/post links at rock.errtheblog.com – some are 404ing.

  • wildchild, about 10 hours later:

    Beafuckingutiful! MS DLINQ sucks like never before ;)

  • Adam Sanderson, about 10 hours later:

    Delaying execution until the kicker method is a real big winner for cached content. Looks really cool :)

  • Bryan Duxbury, about 11 hours later:

    This is some pretty cool stuff. However, I think that potentially the most interesting part of the whole bunch is these Query objects that get created. Even without special syntax for defining the find conditions, this is a huge gain for caching, as mentioned. Is there any chance this functionality would be factored out from the Enumerable-type stuff?

  • PJ, about 13 hours later:

    For the people that like this because of the delayed execution, there’s a simple enough way to do this now.

    controller: 
    @users = proc { User.find(:all) }
    view: 
    <% cache do %>
  <% render :partial => @users.call %>
<% end %>
  • PJ, about 13 hours later:

    Another way to accomplish that is to do something like:

    controller: 
    helper_method :all_users
def all_users
  @all_users ||= User.find(:all)
end
    view: 
    <% cache do %>
  <% render :partial => 'users', :collection => all_users %>
<% end %>

    Pick your poison.

  • Sam Smoot, about 14 hours later:

    I’d love to bump heads with you guys some day and figure out how to take advantage of some of this. Sharon Rosner (Sequel) has been doing something similar, but performance seems to be a draw-back.

    It’s my hope that eventually becomes a non-issue with Rubinius. :)

    Hmm… I should start a #datamapper on irc.freenode.

  • Kirk Haines, about 15 hours later:

    I’ve been doing this with Kansas, more or less, for quite a while.

    Kansas is a Ruby ORM that doesn’t depends on parsetree and uses DBI for the datbase drivers, and is in production use on a whole bunch of web sites over the last 4.5 to 5 years.

    I don’t hack on it a lot because, while it could benefit from some attention, it’s also proved quite stable, and capable of handling almost everything I need of it.

    Query syntax looks something like this:

    users_over_18 = dbh.select(:User) do |u| u.sort_by(u.age) u.age > 18) end

    It’s lightweight and relatively fast. It supports transactions, relationships, marking results as read-only, so changes to the objects don’t get pushed back to the db, and a few other nifty things.

    The most current snapshot of it is at http://withruby.com/kansas but I have a few minor modification requests from my small pool of users, so a more updated version of it will arrive at your friendly neighborhood rubyforge soonish.

    I also have some (old, but still generally useful) docs at http://enigo.com/projects/kansas

  • Scott Becker, about 16 hours later:

    This is super, super cool. Respec. t.

  • Tony, about 16 hours later:

    This is godly… I never would’ve thought of applying mnesia’s qlc to Enumerable… WELL DONE

  • Sharon Rosner, about 23 hours later:

    This looks like a must-have for using ActiveRecord.

  • Joe, about 23 hours later:

    This is great stuff! I kept looking for someone in to do in Ruby what LINQ is doing in the CLR and actually use the parse tree to build the SQL query.

  • Alfred, about 23 hours later:

    really nice work guys.. thanks for making Rails better for everyone and for sharing your work! The Rails community is growing like crazy because of good stuff like these.

  • riffraff, 1 day later:

    this is really cool, and reminds me of Django’s query system: have you ever looked at it and could contrast them?

  • Alan, 1 day later:

    Hey, this is really fantastic. Looking forward to messing around with it. Thanks!

  • August Lilleaas, 1 day later:

    Like, omg. Like, make it stable and demand to get it into Rails 2.0. Like, seriously, like just, like do it, k?

  • Michael Siebert, 1 day later:

    Awesome! Really f*cking awesome!

    But what about speed? How fast is it compared to “the old way”?

  • Ryan Thompson, 1 day later:

    It’s already been said but this is very very cool! Thanks for the excellent work.

  • Robert Brewer, 1 day later:

    Great to see! This looks exactly what I’ve been doing in Python for a few years now with Dejavu and now Geniusql. I’d love to compare notes sometime about implementation. Or jsut steal my work where needed; it’s all public domain. ;)

    You might also look at GLORP, which seems to do the same for Smalltalk.

  • Mark Vorselle, 2 days later:

    How about adding Enumerable#select_with_index, http://snippets.dzone.com/posts/show/3746 ?

  • dennis, 3 days later:

    holy crap! :o

  • Senthil Nayagam, 4 days later:

    Wow.

    db abstraction to the extreme, will start trying it in my experimental projects. keep up the good work.

  • weepie :...(, 4 days later:

    It’s a shame that the slice shortcut is incompatible with this little hack :

    class <<ActiveRecord::Base alias_method :[], :find end

    I’m not sure which makes more sense though.. !

  • Chris, 4 days later:

    weepie: The SVN version works with that hack. Expect a new gem (and a post about it) soon!

  • weepy, 5 days later:

    Will it be possible to use Ambition as a replacement for :conditions => “user is not null and blah blah blah”

  • Chris, 5 days later:

    weepy: You could do User.select { |u| u.name != nil && blah }, is that what you mean?

  • weepy, 5 days later:

    Sorry I meant something along the lines of :

    has_many :users, :conditions => “name is not null”

  • Malko, 6 days later:

    Any information on getting this installed and working on a host without root access? I’ve tried unpacking and using gemsonrails, but I think it’s a problem in the way parsetree and such is required.

    Any suggestions?

  • ryan, 7 days later:

    zomfg!!! :P

    Thanks guys!

  • grant, 7 days later:

    you know what would be cool? Adding has_finder functionality, with all the benefits of the kicker methods!

    Article.published.popular.first

    http://www.pivotalblabs.com/articles/2007/09/02/hasfinder-its-now-easier-than-ever-to-create-complex-re-usable-sql-queries

  • Wiliam, 17 days later:

    Rawk! This is great, and I look forward to playing with it.

    Hopefully it’s a fine step on the road to making my apps more agnostic about persistent storage mechanisms. SQL databases are great and all, but sometimes other choices are better.

  • jercoli, about 1 month later:

    Nice job!!, like a LINQ for .NET, that’s the way to access data.

  • Coren, about 1 month later:

    Great ! It seems that your ambition can take off the ground in the near future ;).

  • Sam Figueroa, 3 months later:

    So can anybody clarify if this is MySQL/SQLite only at the time.

  • Paul Alzire, 5 months later:

    sort_by receiving blocks is weird… it couldn’t just accept a string or an array?

    And has_finder functionality would be awesome! The link someone put above, but here it goes again:

    http://www.pivotalblabs.com/articles/2007/09/02/hasfinder-its-now-easier-than-ever-to-create-complex-re-usable-sql-queries

  • Chris, 5 months later:

    Paul Alzire We’re not adding anything to Ruby. sort_by accepts blocks in Ruby, not strings. Check the newest post for more information.

  • lllyq, 6 months later:

    about inner join, there is a referrence

    http://code.google.com/p/rquerypad/

  • Sixty-one people have commented.
    Chime in.
    Sorry, no more comments :(
This is Err, the weblog of PJ Hyett and Chris Wanstrath.
All original content copyright ©2006-2008 the aforementioned.