Searching with a collection


#1

If I want to find all rows in a table where a field matches one of
several possible values, what is the most efficient way to do it?

So I have table groups, with field idcode
I have idcodes = [143, 482, 941]
I’m guessing I should do:

find(:all, :conditions => idcodes.collect {|id| “idcode = #{id}”}.join("
OR "))

Oh, and I suppose instead of making two posts, since they are directly
related, I can just ask another question here also:

I’m going to be getting the array of idcodes from another table, called
idents.

Something like table idents, with fields name, code1, code2, idcode.

I want to get an array of all the idcodes where the row fits some other
condition… should I do this via find(:all, :conditions => “code1 = 2
AND code2 = 4”).collect {|ident| ident.idcode}, or is there some other,
better way to get an array containing just the values of one field of a
table, without having to get each record and pull that field from it?

Thanks.


#2

find :all, :conditions => [ ‘idcode IN (?)’, idcodes]

find(:all, :conditions => “…”, :select => ‘idcode’).map(&:idcode)

  -- Jean-Fran�ois.

Thank you very much :slight_smile:
Those will both work perfectly.


#3

Hi Luke,

If I want to find all rows in a table where a field matches one of
several possible values, what is the most efficient way to do it?

So I have table groups, with field idcode
I have idcodes = [143, 482, 941]
I’m guessing I should do:

find(:all, :conditions => idcodes.collect {|id| “idcode = #{id}”}.join("
OR "))

find :all, :conditions => [ ‘idcode IN (?)’, idcodes]

is better.

And don’t use string interpolation in tour queries to avoid possible
SQL injection.

AND code2 = 4").collect {|ident| ident.idcode}, or is there some other,
better way to get an array containing just the values of one field of a
table, without having to get each record and pull that field from it?

if you’re only interested in idcode attribute, I can only see to
fetch only idcode attribute with :select option like this :

find(:all, :conditions => “…”, :select => ‘idcode’).map(&:idcode)

the map(&:idcode) is the same as your collect … but shorter :slight_smile:

  -- Jean-François.


Ruby ( http://www.rubyfrance.org ) on Rails ( http://www.railsfrance.org
)