ActiveRecord.find([id array]) question


#1

If you have a collection of ids, what’s the best way to extract the
records from the database while maintaining the original id order?

Recently I noticed that a find statement like

% User.find([1, 2, 3])

will return the same thing as

% User.find([3, 2, 1])

…ignoring the order of the ids in the array completely. In my case, I
have an array that is twenty ids long and I need the ordering preserved.
What’s the best solution without having to loop through the ids and
select each record one by one?


#2

Ryan M. wrote:

…ignoring the order of the ids in the array completely. In my case, I
have an array that is twenty ids long and I need the ordering preserved.
What’s the best solution without having to loop through the ids and
select each record one by one?

It’s a SQL limitation, you can’t enforce any order unless you actually
use ORDER BY (:order parameter of ActiveRecord::Base.find).

You could create a hash ‘id => position’ from your original id array and
use it to sort the results according to their id.

Lionel


#3

ids = [ 3, 1, 2 ]

results = User.find(ids)
sorted_results = results.sort_by {|u| ids.index(u.id) }

How do you do pagination with the above? Specifically with the
will_paginate plugin? Is it possible? will_paginate is used in place of
find, yet the sort is happening after the find…

BTW, this may be a SQL limitation, but as far as I can tell from
development.log, Rails does still change the order of the array
(numerical ascending). Not sure why… that’s certainly not expected
behavior. Unless perhaps it optimizes performance for the query?


#4

On Jul 27, 2007, at 6:57 PM, Ryan M. wrote:

…ignoring the order of the ids in the array completely. In my
case, I
have an array that is twenty ids long and I need the ordering
preserved.
What’s the best solution without having to loop through the ids and
select each record one by one?

You could search for past examples of this very question for
starters, but here’s a technique that will give you the result that
you seek:

ids = [ 3, 1, 2 ]

results = User.find(ids)
sorted_results = results.sort_by {|u| ids.index(u.id) }

-Rob

Rob B. http://agileconsultingllc.com
removed_email_address@domain.invalid


#5

BTW, this may be a SQL limitation, but as far as I can tell from
development.log, Rails does still change the order of the array
(numerical ascending). Not sure why… that’s certainly not expected
behavior. Unless perhaps it optimizes performance for the query?

Whoops, sorry to reply to myself, but after looking further I realize I
am wrong about the above.

Still trying to solve the find/array/paginate puzzle though…

Carl