Targetting associated tables in an ActiveRecord conditions hash

This entry was posted February 5 2009 under Rails, Web Development.

8 comments

Using a conditions hash isn’t always my favourite way to write finds in Rails, but in certain cases it’s an indispensible tool for building queries.  It’s especially handy when you’re building an advanced search where conditions may or may not be present.

Recently I ran into an issue where I needed to run conditions on an associated table in just such a search and I managed to find what seems to be an undocumented (or under-documented at least) feature of ActiveRecord. For the sake of using the same example as every other Rails tutorial out there, let’s assume that I’m running a search on Authors, and that an Author has_many books. All we have to do is make a hash within the conditions hash with the name of the associated table. Searching both tables is easy as:

Author.find(:conditions => {:first_name => params[:first_name], :last_name => params[:last_name], :books => { :title => params[:book_name] } }, :include => :books)

If we were writing this as an advanced search and wanted the parameters to be optional, we would break it out like this:

conditions = {}
conditions[:first_name] = params[:first_name] unless params[:first_name].blank?
conditions[:last_name] = params[:last_name] unless params[:last_name].blank?
conditions[:books] = {:title => params[:book_name]} unless params[:book_name].blank?

@authors = Author.find(:conditions => conditions, :include => :books)

Simple!

Comments


nachokb on February 5th, 2009
Have a look at Searchlogic [1], it takes hash conditions to a new level... -- nachokb [1] http://github.com/binarylogic/searchlogic
Ever try Squirrel, from ThoughtBot? http://thoughtbot.com/projects/squirrel This allows you to pass a block to ActiveRecord's find method and do some really pretty queries in ruby. js

lolcatz on February 5th, 2009
You should check out Ambition. It gives you more rubyish syntax.
I have been trying to use hash as a conditions but it doesn't seem to work. Here is my code. documents = Document.find(:all, :include => :entity, :conditions => {:entities => {:name => "AFY"}}) I get the following error. "ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'entity.name' in 'where clause': SELECT * FROM `documents` WHERE (`entity`.`name` = 'AFY')" What am i doing wrong? Also would it work if i have the include nested a level deeper? like so: documents = Document.find(:all, :include => {:entity => :accesses}, :conditions => {:entities => {:accesses => {:user_id => user.id}}}) I'am on rails 2.3.2, thanks.
Hi Federico, Try :entity => {:name => "AFY"} instead of :entities => {:name => "AFY"}. Failing that, make sure your relationships are set up properly at the model level.

Steve on April 29th, 2009
I'm having the same trouble: Active Record is saying "unknown column". If I build it the literal way with a string like: conditions => 'entities.name . . .' then it works just fine, so I don't think it's a problem with the model set-up. Any thoughts appreciated

Steve on April 29th, 2009
The relationship I'm using is polymorphic - I wonder if there are issues with that . . .
That's entirely possible. Try taking a look at the SQL that's produced and see if it makes sense. You may need to use :joins instead of :include, I've never used this functionality on a polymorphic association.

Post a Comment







OMG Who?

I'm Sean Roberts, a 24-year-old Ruby on Rails developer currently working for Hamilton web design firm factor[e] design initiative. This is my blog about work and nerd stuff, and maybe a little bit about music and food too.

I'm On Twitter!

  • Can anyone recommend a good tailor in Hamilton? 2 weeks ago
  • Alphabits are a highly underrated cereal. Just saying. 2 weeks ago
  • Oh look, another good deal on a product that I need and can't find at a store around here. Do they ship to Canada? Of course not. 3 weeks ago
  • RT @JayHiggins: I'm going to be positive: Games big success, Ticats sold to billionare, LRT, Condo Dev at WH, Bettman retires 3 weeks ago
  • Wait, Hamilton has a football team? 3 weeks ago
  • Hooray for west harbour! I promise to not be sarcastic RE: government for a couple days. Probably. 3 weeks ago
  • More updates...