ActiveRecord find with conditions: = works but IN fails

rubyonrails-talk
1

Hi,

I'm confused trying to get a simple conditional find working and would greatly appreciate any insight into what might be happening here. I have ='s working ok but 'IN' is failing.

I have 2 models:

class Site < ActiveRecord::Base   has_many :chargers

class Charger < ActiveRecord::Base belongs_to :site

I'm trying to simply filter chargers.connector by one or more connector values. I can do this with SQL no problem but I'm really hoping to understand why I can't get this working with ActiveRecord:

This Works - search by a single value: sitesinrange = Site.find(:all, :include => :chargers, :conditions => ["chargers.connector = ?", "a"])

This Works -searching by a fixed number of values: siteinrange = Site.find(:all, :include => :chargers, :conditions => ['chargers.connector = ? OR chargers.connector = ?', "A","B"])

This Fails - Trying to use "in" with an array: sitesinrange = Site.find(:all, :include => :chargers, :conditions => ["chargers.connector in ?", ["a","b"]])

Here's the hopefully relevant portions of the SQL statement that is logged:

ActiveRecord::StatementInvalid: [snip] FROM `sites` LEFT OUTER JOIN `chargers` ON chargers.site_id = sites.id WHERE (charger.connector in 'a','b')

And the stack:

from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/ connection_adapters/abstract_adapter.rb:219:in `rescue in log'   from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/ connection_adapters/abstract_adapter.rb:202:in `log'   from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/ connection_adapters/mysql_adapter.rb:323:in `execute'   from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/ connection_adapters/mysql_adapter.rb:608:in `select'   from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/ connection_adapters/abstract/database_statements.rb:7:in `select_all'   from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/ connection_adapters/abstract/query_cache.rb:62:in `select_all_with_query_cache'   from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/ associations.rb:1617:in `select_all_rows'   from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/ associations.rb:1395:in `block in find_with_associations'   from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/ associations.rb:1393:in `catch'   from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/ associations.rb:1393:in `find_with_associations'   from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/base.rb: 1546:in `find_every'   from /ruby/1.9.1/gems/activerecord-2.3.5/lib/active_record/base.rb: 615:in `find'   from (irb):3   from /usr/bin/irb:13:in `<main>'

Thanks much!

2

This Fails - Trying to use "in" with an array: sitesinrange = Site.find(:all, :include => :chargers, :conditions => ["chargers.connector in ?", ["a","b"]])

that needs to be (?) - the interpolation bit of Active Record doesn't know the wider context of what you are doing so doesn't know to add this (see also the hash form of conditions - it knows whether to use =, IN (...) or IS NULL as appropriate)

Fred

3

Thank you! Works perfectly and more importantly I really appreciate the explanation.

Cheers,

Nick