Well, as the inaugural post to my blog, I figured I’d write about something that I had some trouble finding when I went looking for it some time back:  Dynamically building ActiveRecord queries in Ruby on Rails using the Squeel gem.

If you’re like me, you may have thoroughly enjoyed the vanilla ActiveRecord methods for the simplicity of the arguments they accept.  If you wanted to, you could build a hash using completely arbitrary means and then throw that hash over to an ActiveRecord “where” method and watch the magic happen.  Ernie Miller‘s brilliant MetaWhere gem (Squeel’s predecessor) built on that functionality by extending the very basic workings of symbols, which maintained for us the ability to pass such elegantly arbitrary arguments, only with greatly enhanced query building capabilities.  It is amazing, and anyone using Rails ~>3.0 should look into it.

However, you may have upgraded your app at some point to Rails 3.1+ to take advantage of all the new features, only to find out that MetaWhere is not supported in this version and you must switch to Squeel instead.  It will quickly become apparent to you that the way Squeel functions, using its domain-specific language (DSL) to contextualize all the SQL query building, does not allow for building your query through arbitrary means prior to using it in Squeel’s DSL context.  It must all be done inside of Squeel blocks passed to ActiveRecord methods.  That’s not to say it isn’t a great gem that does amazing things—it certainly is.  It just can’t be used the way I wanted to use it.  Or so I started to think…

Actually, you can simulate Squeel’s DSL in any context by using Squeel’s classes directly.  For instance, you can do things like:

# Returns a Squeel DSL object similar to:
# name.matches("%#{my{in_name}}%")
def query_for_name_matches(in_name)
  name_stub = Squeel::Nodes::Stub.new(:name)
  Squeel::Nodes::Predicate.new(name_stub, :matches, "%#{in_name}%")
end

You can then use it like this:

q = "Bob"
User.where(query_for_name_matches(q)) # Note: passed in parens ()

That’s pretty cool, but how far can we take this?  How do I use keypaths like posts.author.name?

# Note how these lines reference relative paths completely out of
# context. The context and meaning of these statements are
# evaluated at the time of use and no earlier, which is perfect for
# our purposes.
def query_for_authored_by(in_name)
  posts_join = Squeel::Nodes::Join(:posts)
  author_join = Squeel::Nodes::Join(:author)
  author_kp = Squeel::Nodes::Keypath(posts_join, author_join)
  name_kp = Squeel::Nodes::Keypath(author_kp, :name)
  Squeel::Nodes::Predicate(name_kp, :eq, in_name)
end

What about SQL functions, which Squeel supports?

lowername = Squeel::Nodes::Function.new(:lower, [name_kp])
# Note that it uses an array as the second argument

What about something crazy with an arbitrary number of ‘or’ statements and an outer join, maybe in a hash format?

def query_for_authors_named(*args)
  name_stub = Squeel::Nodes::Stub.new(:name)
  my_pred = args.inject(nil) do |running_pred, name|
    this_pred = Squeel::Nodes::Predicate.new(name_stub, :matches, "%#{name}%")
   #this_pred = query_for_name_matches(name)  # Remember?
    if running_pred
      running_pred | this_pred
    else
      this_pred
    end
  end

  {Squeel::Nodes::Join.new(:authors, Arel::OuterJoin) => my_pred}
end

> Post.where(query_for_authors_named("Bob", "Robert", "Tony"))

So you can see that all the power of Squeel is still there, as long as you know where to look.  It may be possible to do most of your queries on the spot in the Squeel DSL, but there are certainly times when it’s more convenient to piece things together externally.  I hope this helps someone!