Forum: Ruby on Rails Using conditions in ActiveRecord

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
11c6b28a5ceed24457944b054cce2786?d=identicon&s=25 David Adams (Guest)
on 2006-01-18 22:56
(Received via mailing list)
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.
132a94ca65959bda6c74fae54bff2425?d=identicon&s=25 Ezra Zygmuntowicz (Guest)
on 2006-01-18 23:02
(Received via mailing list)
On Jan 18, 2006, at 1:16 PM, David Adams 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 Zygmuntowicz
Yakima Herald-Republic
WebMaster
http://yakimaherald.com
509-577-7732
ezra@yakima-herald.com
3dd4b52a0946bd698b1d1635a46ea3a3?d=identicon&s=25 Francois Beausoleil (Guest)
on 2006-01-18 23:05
(Received via mailing list)
2006/1/18, David Adams <da@dsc.net>:
> 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 !
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 Brian V. Hughes (Guest)
on 2006-01-18 23:20
(Received via mailing list)
Ezra Zygmuntowicz 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
11c6b28a5ceed24457944b054cce2786?d=identicon&s=25 David Adams (Guest)
on 2006-01-19 12:25
(Received via mailing list)
> 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.
Ad7805c9fcc1f13efc6ed11251a6c4d2?d=identicon&s=25 Alex Young (Guest)
on 2006-01-19 12:34
(Received via mailing list)
David Adams 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]

?
11c6b28a5ceed24457944b054cce2786?d=identicon&s=25 David Adams (Guest)
on 2006-01-19 16:35
(Received via mailing list)
> 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.
This topic is locked and can not be replied to.