Hello all. I have two models Resource and Department and a Resource belongs to a Department. I wan to allow th user to search Resources that are present in some Departments using the departments type column, so I do a find like this in the Resource controller:: documents = Department.find(:all, :conditions => ["type=?", 4]) resources = Resource.find(:all, :conditions => ["document_id IN (?)", documents]) but resources variable is always an empty array. Can anyone tell me how to overcome this problem? Thanks in advance. Best regards, Hugo Magalhães
Sorry the code was wrong. Here is the right code: departments = Department.find(:all, :conditions => ["type=?", 4]) resources = Resource.find(:all, :conditions => ["department_id IN (?)", departments]) On 8/26/05, Hugo Magalhaes <hugo.mag-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hello all. > I have two models Resource and Department and a Resource belongs to a > Department. > I wan to allow th user to search Resources that are present in some > Departments using the departments type column, so I do a find like > this in the Resource controller:: > > documents = Department.find(:all, :conditions => ["type=?", 4]) > resources = Resource.find(:all, :conditions => ["document_id IN (?)", > documents]) > > but resources variable is always an empty array. Can anyone tell me > how to overcome this problem? > > Thanks in advance. > Best regards, > Hugo Magalhães >
> departments = Department.find(:all, :conditions => ["type=?", 4]) > resources = Resource.find(:all, :conditions => ["department_id IN > (?)", departments])check the logs -- see what sql query its translating those to.
Hello Caleb. Thanks for your help. I''ve search the log and found out that the select in the resources table was done using department ruby objects and not with the department_id, so I changed the code, but I don''t know if this is the best way to to this. Here is the code: departments = Department.find(:all, :conditions => ["type=?", 4]) department_is = Array.new department.each { | department | department_ids.push(department.id) } resources = Resource.find(:all, :conditions => ["department_id IN (?)", department_ids]) On 8/26/05, Caleb Buxton <adbust-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > departments = Department.find(:all, :conditions => ["type=?", 4]) > > resources = Resource.find(:all, :conditions => ["department_id IN > > (?)", departments]) > > check the logs -- see what sql query its translating those to. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Hi, try it with only the ID´s in an array. I`m using this way too and it works with the only the IDs in an array. doc_ids = documents.collect{|doc| doc.id}> resources = Resource.find(:all, :conditions => ["document_id IN > (?)",doc_ids])Regards, Oliver Dohmen, RoR Beginner Am 26.08.2005 um 01:03 schrieb Hugo Magalhaes:> Hello all. > I have two models Resource and Department and a Resource belongs to a > Department. > I wan to allow th user to search Resources that are present in some > Departments using the departments type column, so I do a find like > this in the Resource controller:: > > documents = Department.find(:all, :conditions => ["type=?", 4]) > resources = Resource.find(:all, :conditions => ["document_id IN (?)", > documents]) > > but resources variable is always an empty array. Can anyone tell me > how to overcome this problem? > > Thanks in advance. > Best regards, > Hugo Magalhães > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
> Hello Caleb.Hey Hugo> Thanks for your help. I''ve search the log and found out that the > select in the resources table was done using department ruby objects > and not with the department_id, so I changed the code, but I don''t > know if this is the best way to to this. Here is the code: > > departments = Department.find(:all, :conditions => ["type=?", 4]) > department_is = Array.new > department.each { | department | > department_ids.push(department.id) > } > resources = Resource.find(:all, :conditions => ["department_id IN > (?)", department_ids])Sorry, yeah, I didn''t look very closely you''ve got a one2many relationship with Departments and Resources right? Why don''t you go... departments = Deparment.find(:all,:conditions=>["type=?",4],:include=>[:resources]) resources = departments.collect {|d| d.resources} that way its only one query to the db... rather than like 2. Further -- if those numbers somewhere translate to names, why not use single table inheritance? that way you could just do type4departments = Type4Deparment.find(:all,:include=>[:resources]) etc... or like = Accounting.find(:all ..... anyways -- just some thoughts. HTH -C
Hugo Magalhaes wrote:>Sorry the code was wrong. Here is the right code: >departments = Department.find(:all, :conditions => ["type=?", 4]) >resources = Resource.find(:all, :conditions => ["department_id IN >(?)", departments]) > > >As was pointed out in an earilier post departments returns an array of Department instances so you want something like: dept_ids = Department.find(:all, :conditions => ["type=?", 4]).map{|d| d.id} to get an array of dept_ids then your conditions needs to either have a ? for each dept_id: bind_array = [''?''] * dept_ids.size bind_str = bind_array.join('','') resources = Resource.find(:all, :conditions => ["department_id in bind_str, dept_ids] ) or you need to build the in clause yourself: resources = Resource.find(:all, :conditions => ["department_id in (#{deptids.join('','')})])