Forum: Ruby on Rails Order of results from find(array)

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
B0ff75d809fdbc09adaba01ffb451c70?d=identicon&s=25 Dave (Guest)
on 2007-07-05 15:02
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!
Aff7605ddfa1b4c6b580089dea653a55?d=identicon&s=25 John Browning (Guest)
on 2007-07-05 16:05
(Received via mailing list)
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.
Ef3aa7f7e577ea8cd620462724ddf73b?d=identicon&s=25 Rob Biedenharn (Guest)
on 2007-07-05 17:31
(Received via mailing list)
On Jul 5, 2007, at 10:04 AM, John Browning 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 Biedenharn    http://agileconsultingllc.com
Rob@AgileConsultingLLC.com
58c6efb8466b9f85155fe6aa9fc37fce?d=identicon&s=25 Chris Taggart (christ)
on 2007-07-05 21:29
(Received via mailing list)
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
This topic is locked and can not be replied to.