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! -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Frederick Cheung
2009-Dec-10 00:46 UTC
Re: ActiveRecord find with conditions: = works but IN fails
On Dec 10, 12:30 am, NickW <nickw...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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> 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!-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Thank you! Works perfectly and more importantly I really appreciate the explanation. Cheers, Nick On Dec 9, 4:46 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Dec 10, 12:30 am, NickW <nickw...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > 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 > > > > > 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!-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.