Using an arrray as a parameter value in ActiveRecord

Rails
1

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

2

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.

3

make that “values are safe”…I need to go to bed :slight_smile:

4

William P. 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 :slight_smile:

5

Peter M. 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

6

On 9/24/07, Roderick van Domburg [email protected]
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

7

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.