Back in January of 2006 Ezra Zygmuntowicz came up with an exceptionally cool Rails plugin that, IMNSHO, should be in rails core. It’s called ez_where and it’s svn repo is here.

What’s so cool about ez_where? Well, the to really understand it’s beauty you have to step back to one of core concepts that’s at the heart of frameworks like Rails: Database Abstraction and Object Relational Mappings. Before these concepts really came into their own we were all writing raw SQL commands in our apps. Now we interact with a layer of abstraction that lets us work with the objects we’re actually programming with instead of database structures. Except, that’s not quite true with Rails. With rails you end up writing things that are possibly more complex than the SQL statements we’ve been trying to avoid. Here’s a particularly egregious example from a ticket system I’ve been working on:

     Ticket.find(:all,
         :conditions=>["closed = ? AND product_id in (?) AND user_id = ?  
             AND ticket_type_id=? ", product_ids, user_id, ticket_type_id],
         :order=>["weighted_vote_percentage DESC, ticket_id DESC"])

Personally I don’t see this as being particularly better than writing the raw sql since I am writing the raw sql. Yes Rails has saved me from writing the SELECT t.* from tickets t WHERE but then it forced me to write the entire where and order clauses and put them in a data contstruct that took more thinking and more characters than the SQL would have. How is this better? SQL is database agnostic anyway (unless you start adding in vendor specific additions ) so it’s not really buying me separation from that… So what is it buying me?

Ezra gets it though. If you use his plugin you’ll be able to use the power of ruby in your find statements. Yes, it may be more wordy than SQL but what it buys you is a true separation from your database. His blog posting on ez_where gives this great example:

 Model.find_with_conditons( :all, :limit => ..., :order => ... ) do 
   foo == 'bar'       # ["foo = ?", 'bar]
   baz <=> (1..100)   # ["baz BETWEEN ?AND ?", 1,100]
   woo =~ 'substri%'  # ["woo LIKE ?", 'substri%']
   id === (1..4)      # ["id IN (?)", [1,2,3,4]]
   fiz < 10           # lt, gt, leq, geq, etc., should all "just work" 
 end

Now, to be totally up front with you, I haven’t actually used this plugin yet, but, honestly, I don’t even care if it’s buggy. If it’s even remotely close to working I’ll be helping to fill in any holes that present themselves. For years experienced developers have been trying to get us to remove SQL from our code, and then Rails comes along and chucks that advice out the window. This, or something very much like it, is what we should be moving towards.