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

Comments

Leave a response

  1. ippa said 1 day later:
    This is so cool. I thought vanilla AR was a big step up from years of unmaintainable raw SQL-queries, but this really takes it one step further =). An idea, maybe ruby regexp: article.title =~ /^regexp/ Could transform into mysql regexp: SELECT * FROM article WHERE title REGEXP "^regexp"
  2. Ezra said 4 days later:
    Ask and ye shall receive ;) That works now in trunk.
  3. sole said 11 days later:
    That's absolutely amazing! I'm by now confined to use cakephp which is kind of rails phpified but due to php's nature I really doubt this kind of things can ever be accomplished in php. I just can't count the days until I manage to get rid of all things php and start using these magic daily. Makes life easier.
  4. kerforn56@wanadoo.fr said 35 days later:
    Cool plugin... as any newbrails.. I have been first into 'testing' .. unit tests OK, but could not run the functional tests : right after fixtures init, I got : /usr/local/lib/ruby/gems/1.8/gems/actionpack-1.12.5/lib/action_controller/test_process.rb:21:in `alias_method': undefined method `process' for class `ActionController::Base' (NameError) ... from vendor/plugins/ez-where/test/ez_where_controller_test.rb:2 which is the line : require 'action_controller/test_process' any clue ? thanks erwin
  5. alexl said 55 days later:
    What about SUM/COUNT/MIN/MAX, GROUP BY, and HAVING?
  6. brthrmnss AT hot mail .com said 117 days later:
    Ezra, any chance we could fix the issue with:

    When a model has two associations to the same table with different association names, ez_find breaks down because it doesn't use the table alias for the second table.

    E.g.,
    class Item belongs_to :seller, :class_name=>'User' belongs_to :buyer, :class_name=>'User' end You can't do: Item.ez_find(:all, :include=>[:buyer, :seller]) do |item, buyer, seller| buyer.login =~ 'Ez%' seller.login =~ 'Kevin%' end

    The ez condition generator creates a where clause like: WHERE ((users.login LIKE 'Ez%') AND (users.login LIKE 'Kevin%'))

    That second one is actually aliased within the join in Rails 1.1 to "sellers_items". The ez finder should actually generate: WHERE ((users.login LIKE 'Ez%') AND (sellers_items.login LIKE 'Kevin%'))

RSS feed for this post trackback uri

(leave url/email »)

   Preview comment