Hi all, I''m using find to query my database using an array of IDs that represent the records I want to pull. But when the results come back they are in a different order than that I requested them in. For instance, I have this array of IDs: ids = [6, 4, 1, 5, 2, 3] I want to pull the records with these IDs from the database so I use: @records = Model.find(ids) The result from this query comes back in numerical order (which is what I don''t want), so the records would be ordered: [1, 2, 3, 4, 5, 6] *not* what I want, I really want the same order: [6, 4, 1, 5, 2, 3] is there any way to maintain the order of the records? So the order of the IDs in the array I requested the records with? Thanks in advance! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 5 Jul 2007, at 14:02, Dave wrote:> > Hi all, > > I''m using find to query my database using an array of IDs that > represent > the records I want to pull. But when the results come back they are > in a > different order than that I requested them in. > > For instance, I have this array of IDs: > > ids = [6, 4, 1, 5, 2, 3] > > I want to pull the records with these IDs from the database so I use: > > @records = Model.find(ids) > > > The result from this query comes back in numerical order (which is > what > I don''t want), so the records would be ordered: > > [1, 2, 3, 4, 5, 6] > > *not* what I want, I really want the same order: [6, 4, 1, 5, 2, 3] > > is there any way to maintain the order of the records? So the order of > the IDs in the array I requested the records with? > > Thanks in advance! > > -- > Posted via http://www.ruby-forum.com/. > > > >Probably depends what the order of the array is. If it''s arbitrary, there is no easy way to get find''s SQL to return the same order. So probably the next best is just to cycle through each element of the array and grab the corresponding record from what is returned by find. If there''s some way of describing the order in terms of the underlying table (eg, by date), then use :order => condition with find. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Jul 5, 2007, at 10:04 AM, John Browning wrote:> On 5 Jul 2007, at 14:02, Dave wrote: >> Hi all, >> >> I''m using find to query my database using an array of IDs that >> represent >> the records I want to pull. But when the results come back they are >> in a >> different order than that I requested them in. >> >> For instance, I have this array of IDs: >> >> ids = [6, 4, 1, 5, 2, 3] >> >> I want to pull the records with these IDs from the database so I use: >> >> @records = Model.find(ids) >> >> >> The result from this query comes back in numerical order (which is >> what >> I don''t want), so the records would be ordered: >> >> [1, 2, 3, 4, 5, 6] >> >> *not* what I want, I really want the same order: [6, 4, 1, 5, 2, 3] >> >> is there any way to maintain the order of the records? So the >> order of >> the IDs in the array I requested the records with? >> >> Thanks in advance! > > Probably depends what the order of the array is. If it''s arbitrary, > there is no easy way to get find''s SQL to return the same order. So > probably the next best is just to cycle through each element of the > array and grab the corresponding record from what is returned by > find. If there''s some way of describing the order in terms of the > underlying table (eg, by date), then use :order => condition with > find.Use a sort_by on the results and the Array#index of the id from your original array of model IDs as the value on which to sort. >> ids = [36, 27, 35] => [36, 27, 35] >> products = Product.find ids => [...stuff...] >> products.size => 3 >> products.map(&:id) => [27, 35, 36] >> products.sort_by {|p| ids.index(p.id)}.map(&:id) => [36, 27, 35] >> ids => [36, 27, 35] >> The result in "your" code is: @records = Model.find(ids).sort_by {|m| ids.index(m.id)} -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Dave wrote:> Hi all, > > I''m using find to query my database using an array of IDs that represent > the records I want to pull. But when the results come back they are in a > different order than that I requested them in. > > For instance, I have this array of IDs: > > ids = [6, 4, 1, 5, 2, 3] > > I want to pull the records with these IDs from the database so I use: > > @records = Model.find(ids) > > > The result from this query comes back in numerical order (which is what > I don''t want), so the records would be ordered: > > [1, 2, 3, 4, 5, 6] > > *not* what I want, I really want the same order: [6, 4, 1, 5, 2, 3] > > is there any way to maintain the order of the records? So the order of > the IDs in the array I requested the records with? > > Thanks in advance! > >If you''re using MySQL you might want to investigate FIELD. This is from memory by I managed this with something like :order =>"FIELD(my_table.id, ''6, 4, 1, 5, 2, 3''" No idea about other DBs Hope this helps Chris -- ---------------------------- Autopendium :: Stuff about old cars http://autopendium.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---