What's New in Edge Rails: Using Collections of Records as the "IN" SQL Condition

Posted by ryan
at 9:45 AM on Thursday, June 01, 2006

It’s currently somewhat difficult to use the IN SQL condition within ActiveRecord’s finder methods – most solutions I’ve seen involve building your own SQL string or popping back out to the object level and performing more queries than necessary to get around this limitation. Changesets 4391, 4387 and 4390 let you now pass in a collection of ActiveRecord objects as a parameter to the SQL condition that will be expanded to a comma-seperated list of ids.

So, if you have a collection of posts and want to get all comments for those posts, this finder call:

comments = Comment.find(:all, :conditions => ['post_id IN (?)', posts])

Will result in this SQL:

SELECT * FROM COMMENTS WHERE POST_ID IN (1, 2, 3, ..)

Ya jive? You’re now able to pass in a collection of records to your SQL finder methods that will be automatically expanded into a comma-seperated list of ids. This makes it much easier to use the IN (?) SQL condition within ActiveRecord.

Parallel to this functionality is the addition of the Array.to_s(:db) method which does basically the same thing, converts an array of ActiveRecord objects into a comma-seperated list of ids (this is what changeset 4387 adds). This addition makes the following identical to the Comment.find(...) call mentioned earlier:

comments = Comment.find(:all, :conditions => ['post_id IN (?)', posts.to_s(:db)])

Surprisingly, the implementation of the expansion of an ActiveRecord array does not utilize Array.to_s(:db)...

rubyonrails, rails

Comments

Leave a response

  1. Ryan Daigle – May 24, 2006 @ 07:27 AM
    choonkeat, are you referring to a database limit placed on the number of parameters the IN condition can have? I don't believe there's an ActiveRecord-mandated limit...
  2. choonkeat – May 24, 2006 @ 07:27 AM
    Yea. Limit in the SQL (implementation?). I do remember hitting a limit before...
  3. choonkeat – May 24, 2006 @ 07:27 AM
    Hmm. isn't there a limit on the size of the array that can specified after IN?
  4. Michele – May 24, 2006 @ 07:27 AM
    Currently, I achieve this quite easily: comments = Comment.find(:all, :conditions => ['post_id IN (?)', posts.collect {|p| p.id}])

    But this new feature will make it much easier - and more beautiful. ;)

  5. Ryan Daigle – May 24, 2006 @ 10:16 AM
    Michele, so I guess Rails already transforms an array of ids into a comma delimited list - suitable for use in an IN clause. Good to know (though the new way is still preferable).
  6. Ryan Daigle – May 24, 2006 @ 10:16 AM
    Hey Michele,

    Doesn't your posts.collect example only return an array of the post ids? Don't you want:

    comments = Comment.find(:all, :conditions => ['post_id IN (?)', posts.collect {|p| p.id}.join(",")])

    to get a comma delimited list (note the join(",") part)?