Order of results from find(array)


#1

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!


#2

On 5 Jul 2007, at 14:02, Dave wrote:

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.


#3

On Jul 5, 2007, at 10:04 AM, John B. wrote:

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 B. http://agileconsultingllc.com
removed_email_address@domain.invalid


#4

Dave wrote:

I want to pull the records with these IDs from the database so I use:

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