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.
On Jan 18, 2006, at 1:16 PM, David Adams wrote:> 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.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
2006/1/18, David Adams <da@dsc.net>:> 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?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 ! -- Fran?ois Beausoleil http://blog.teksol.info/
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
>> 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]) >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.
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] ? -- Alex #
>> 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]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.