Using conditions in ActiveRecord

Rails
1

This bit of code finds several groups; all those which have
entity_id’s of ‘1’ OR ‘2’ OR ‘3’, as you would expect:

ee = [ 1, 2, 3 ]
groups = Group.find(:all, :conditions => “entity_id in (” + ee.join
(",") + “)”)

But this bit of code only finds groups which have entity_id’s of ‘1’:

ee = [ 1, 2, 3 ]
groups = Group.find(:all, :conditions => [ “entity_id in (?), ee.join
(”,") ] )

Why is that?

Evidently, if I switch the order like this:

ee = [ 3, 2, 1 ]

I only get groups with an entity_id of ‘3’. So clearly if I specify
the parameter like this ‘(?)’, I get only the first element from the
‘ee’ list included in the where clause. Why?

Thanks in advance.

2

On Jan 18, 2006, at 1:16 PM, David A. wrote:

groups = Group.find(:all, :conditions => [ "entity_id in (?),
from the ‘ee’ list included in the where clause. Why?

Thanks in advance.

David-
You can actually pass an array as the value to an IN query like this:

groups = Group.find(:all, :conditions => [ "entity_id in (?),
[1,2,3] ] )

Or

groups = Group.find(:all, :conditions => [ "entity_id in (?), ee] )

Cheers-
-Ezra Z.
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
[email protected]

3

2006/1/18, David A. [email protected]:

groups = Group.find(:all, :conditions => [ “entity_id in (?), ee.join
(”,") ] )

Why is that?

In the second case, Rails “knows” that you are providing a string, so
the resulting SQL will look like:

SELECT * FROM groups WHERE entity_id IN (‘1,2,3’)

Whereas in the first case, Rails knows it’s an array, and will do the
right thing.

Hope that helps !

4

Ezra Z. wrote:

David-
You can actually pass an array as the value to an IN query like this:

groups = Group.find(:all, :conditions => [ "entity_id in (?), [1,2,3] ] )

Or

groups = Group.find(:all, :conditions => [ "entity_id in (?), ee] )

This is the method I always use to accomplish this:

groups = Group.find_all_by_entity_id([1, 2, 3])

-Brian

5

groups = Group.find_all_by_entity_id([1, 2, 3])

Thanks for the suggestions. The plot thickens. If I do this:

ee = [ 4, 9 ]

Then the suggestions above both work:

groups = Group.find(:all, :conditions => [ “entity_id in (?)”, ee] )
or
groups = Group.find_all_by_entity_id(ee)

return what I would expect. However, ee is created like this:

ee = User.find(session[:user_id]).entityadministrators.find(:all).map
{ |e| [e.id]}

My understanding was that that would create an array of entity_id’s.
Indeed, it does seem to and if I join the array entries together with
a comma and then use that result as a string condition in my groups
query, it works. But if I just try to use the array as is, with the
suggestions above, I get no rows returned.

So, the question becomes, what’s the difference between:

ee = User.find(session[:user_id]).entityadministrators.find(:all).map
{ |e| [e.id]} # which returns a 4 and and a 9

and

ee = [ 4, 9 ]

???

Thanks again.

6

David A. wrote:

So, the question becomes, what’s the difference between:

ee = User.find(session[:user_id]).entityadministrators.find(:all).map {
|e| [e.id]} # which returns a 4 and and a 9

and

ee = [ 4, 9 ]

Just a guess:

User.find(session[:user_id]).entityadministrators.find(:all).map { |e|
[e.id]} => [[4], [9]]

User.find(session[:user_id]).entityadministrators.find(:all).map { |e|
e.id} => [4,9]

?

7

User.find(session[:user_id]).entityadministrators.find(:all).map { |
e| e.id} => [4,9]

Right! This works (i.e. removing the extra square brackets in the
first array creation):

@ee = User.find(session[:user_id]).entityadministrators.find
(:all, :order => “id asc”).map { |e| e.id}
@groups = Group.find(:all, :conditions => ["entity_id in (?) ", @ee])

Many thanks.