Targetting associated tables in an ActiveRecord conditions hash
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
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
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.
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
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.
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
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.
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
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.
nachokb on February 5th, 2009