Nested Joins and ez-where update

Posted by ezmobius Tue, 03 Oct 2006 19:58:00 GMT

So Fabien has been at it again and has updated ez-where with some awesome new syntax. You can now use nested joins inside your find_where’s in a much more simple way.

Jon Yurek was inspired by ez-where and built squirrel . Well we were inspired back and decided it was time to finally finish the nested include problems in ez-where. I just egged him on and offered syntax examples but Fabien did all the work on this update and it totally brings things up to a new level. But also it leaves everything fully backwards compatible. This is only possible because we have such a huge test suite in this plugin that lets us refactor and be assured nothing is broken by new additions. Anyways, let’s take a look at some new syntax shall we?

Conditions involving associations are now handled more transparently, while also providing support for conditions on nested includes. 
articles = Article.find_where(:all) do |article|
  article.title =~ 'Lorem%'
  article.author.name  'Ezra'
  article.comments.user.name  ‘Fab’
end
What this does is create the correct options hash and pas it along to ActiveRecord::Base#find. So that query will result in these options to find(the empty {}’s don’t hurt anything and made implementation much easier): 
:include => { :author => {}, :comments => { :user => {} } }
:conditions => ["(articles.title LIKE ? AND (authors.name = ?) 
 AND (users.name = ?))", "Lorem%", "Ezra", "Fab"]

To make the syntax feel even more natural you can do object comparison too. Behind the scenes the actual association is used to deduce the right FK:

user = User.find(1)
options = Article.find_where_options do |article|
    article.comments.user == user
end

which results in:

options[:include] #=> { :comments => { :user => {} } }
options[:conditions] #=> ["(comments.user_id = ?)", 1]
or you can also do: 
users = User.find(1, 2)

options = Article.find_where_options do |article|
  article.comments.user === User.find(1, 2)
end

options[:include] #=> { :comments => { :user => {} } }
options[:conditions] #=> ["(((comments.user_id IN (?))))", [1, 2]]

The new simplified syntax is completely backwards compatible. Also, we’ve split find_where into find_where and find_where_options. This helps to check the generated options, especially when running tests.

It’s possible to use ez_where to build the :order part of the find options. All you need to do is call order! on the column you want to use, optionally passing :asc or :desc (string or symbol).

Doing so automagically includes the right assocations: 
options = Customer.find_where_options do |customer|
    customer.environments.apps.name =~ '%foo%'
    customer.environments.apps.name.order! :desc
end
p options
#=> {:order=>"apps.name DESC",
  :conditions=>["(apps.name LIKE ?)", "%foo%"], 
  :include=>{:environments=>{:apps=>{}}}}

You can also use very complex conditions to create custom queries:
    options = Article.find_where_options do |article|
      article.comments.user.name == 'Fab'
      article.and( (article.c.title =~ 'A%') | (article.c.title =~ 'B%') )
      article.comments.any do 
        body =~ 'Lorem%'
        body =~ 'Ipsum%'
      end
    end

p options
#=> {:include=>{:comments=>{:user=>{}}}, 
:conditions=>["((users.name = ?) AND 
 ((articles.title LIKE ?) OR 
(articles.title LIKE ?)) 
AND (comments.body LIKE ? OR 
comments.body LIKE ?))", 
"Fab", "A%", "B%", "Lorem%", "Ipsum%"]}


Have at it and have some fun. I really like these new features, it makes complex AR find calls super easy to deal with and much nicer to look at. Thanks Fabien for your hard work on it!

get it here:
script/plugin install svn://rubyforge.org/var/svn/ez-where

Tags , ,  | 6 comments