Rails - Sep 2007 - using an arrray as a parameter value in ActiveRecord

Is it possible to use an array as a parameter value? I tried this and
got a mysql error, "Operand should contain 1 column(s)".

class User < ActiveRecord::Base

has_many :locations

def events
   location_ids = []
   for location in self.locations
      location_ids << location.id
   end
   Event.find(:all, :conditions => ["location_id = ?",
location_ids])
end


Thanks in advance.

Peter
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

If you are certain the values are save (as in your example),  I have 
used this many times:

in = "("+self.locations.collect{|loc|
loc.id}.join('','')+")"
Event.find(:all, :conditions => "location_id in #{in}")

As I said before, make sure you know the values are safe or there is an 
obvious sql injection, but for id''s coming from your database, this 
should work well.

Peter Marks wrote:
> Is it possible to use an array as a parameter value? I tried this and
> got a mysql error, "Operand should contain 1 column(s)".
>
> class User < ActiveRecord::Base
>
> has_many :locations
>
> def events
>    location_ids = []
>    for location in self.locations
>       location_ids << location.id
>    end
>    Event.find(:all, :conditions => ["location_id = ?",
location_ids])
> end
>
>
> Thanks in advance.
>
> Peter
>   
--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

make that "values are *safe*"...I need to go to bed :)

William Pratt wrote:
> If you are certain the values are save (as in your example),  I have 
> used this many times:
>
> in = "("+self.locations.collect{|loc|
loc.id}.join('','')+")"
> Event.find(:all, :conditions => "location_id in #{in}")
>
> As I said before, make sure you know the values are safe or there is an 
> obvious sql injection, but for id''s coming from your database,
this
> should work well.
>
> Peter Marks wrote:
>   
>> Is it possible to use an array as a parameter value? I tried this and
>> got a mysql error, "Operand should contain 1 column(s)".
>>
>> class User < ActiveRecord::Base
>>
>> has_many :locations
>>
>> def events
>>    location_ids = []
>>    for location in self.locations
>>       location_ids << location.id
>>    end
>>    Event.find(:all, :conditions => ["location_id = ?",
location_ids])
>> end
>>
>>
>> Thanks in advance.
>>
>> Peter
>>   
>>     
>
> >
>   
--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

William Pratt wrote:
> in = "("+self.locations.collect{|loc|
loc.id}.join('','')+")"
> Event.find(:all, :conditions => "location_id in #{in}")
Thanks William! Values will indeed always be coming in from the db, so 
this works great :)
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Peter Marks wrote:
> Is it possible to use an array as a parameter value? I tried this and
> got a mysql error, "Operand should contain 1 column(s)".
> 
> class User < ActiveRecord::Base
> 
> has_many :locations
> 
> def events
>    location_ids = []
>    for location in self.locations
>       location_ids << location.id
>    end
>    Event.find(:all, :conditions => ["location_id = ?",
location_ids])
> end
What you need is:

  Event.find(:all, :conditions => ["location_id IN (?)",
location_ids])

It''s better to do it that way even if you are certain that the values 
are coming from the database only. In this case it might be a bit 
contrived because you are also pretty sure that it are integers only, 
but generally unescaping may occur whilst reading from the database, and 
you will want to be sure stuff is properly escaped and quoted again.

--
Roderick van Domburg
http://www.nedforce.com
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> What you need is:
> 
>   Event.find(:all, :conditions => ["location_id IN (?)",
location_ids])
> 
> It''s better to do it that way even if you are certain that the
values
> are coming from the database only. In this case it might be a bit 
> contrived because you are also pretty sure that it are integers only, 
> but generally unescaping may occur whilst reading from the database, and 
> you will want to be sure stuff is properly escaped and quoted again.
> 
> --
> Roderick van Domburg
> http://www.nedforce.com
Thanks Roderick. That sounds reasonable. It will also make it more 
concise to do it this way as I will be putting in more requirements.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 9/24/07, Roderick van Domburg
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
> What you need is:
>
>   Event.find(:all, :conditions => ["location_id IN (?)",
location_ids])
And even one more way to do it is:

   Event.find :all, :conditions => { :location_id => location_ids}

which generates the same SQL

--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---