Rails/ActiveRecord: Return order with ActiveRecord#find(Array)
I'm using Ruby on Rails/ActiveRecord and am having trouble with an ActiveRecord#find call. I'm storing in the database a serialized array of recently viewed documents IDs. The IDs are stored in descending order of when they were last viewed, so the most recently viewed document ID is first in the array. The array includes 10 IDs maximum.
So far开发者_开发百科, so good. The problem is that ActiveRecord#find(Array) seems to ignore the order in which the ideas appear in the array. So if I type Document.find([1, 2, 3]), I get the same result as if I do Document.find([3, 2, 1]).
My question, then, is this: how can I get an ActiveRecord result array that is in the same order as the IDs I passed to #find? Or, if ActiveRecord doesn't make this possible directly, how can I sort the resulting Array after the fact?
Thanks so much for any answers people can contribute!
ActiveRecord is an interface to your database and returns the records to you in the same order that the database returns them. If you don't supply an 'order' parameter then the returned order is (effectively) random.
If your order is by id ascending or descending:
results = SomeModelName.find([1,2,3], :order => "id") # ascending order
results = SomeModelName.find([1,2,3], :order => "id desc") # descending order
If id order is not ascending or descending:
ids = [1, 3, 2]
r = SomeModelName.find(ids)
results = ids.map{|id| r.detect{|each| each.id == id}}
a discussion about doing it on the db side, not very good for a rails app :(
[03:24] <brauliobo> how can I select ids in an specified order? i would like to do something like "select * from blocks where id in (349120, 349118, 349117) order by id (349120, 349118, 349117)", but the order return is the db order.
[03:25] <RhodiumToad> if you have only a few ids you can do this:
[03:26] <RhodiumToad> select * from blocks where id in (349120, 349118, 349117) order by id <> 349120, id <> 349118, id <> 349117;
[03:26] <RhodiumToad> if you have more you may prefer this:
[03:27] <RhodiumToad> select * from blocks join (select i, (ARRAY[349120, 349118, 349117])[i] as id from generate_series(1,3) i) s on (s.id=blocks.id) order by s.i;
[03:27] <brauliobo> nice, it is a lot of ids
[03:28] <brauliobo> do you think this second query is "cross" sql compliant?
[03:28] <RhodiumToad> no, it's not
[03:28] <RhodiumToad> there is a standard-compliant way of doing it but that's still not supported by most of the dbs you're likely to use other than postgres
[03:29] <brauliobo> so this is the standard-compliant way?
[03:29] <RhodiumToad> no
[03:30] <RhodiumToad> generate_series is not in the standard
[03:30] <RhodiumToad> the standard way would be something like this:
[03:31] <RhodiumToad> select * from blocks join unnest(array[349120, 349118, 349117]) with ordinality as u(id,i) on (blocks.id=u.id) order by u.i; but pg does not yet support the "with ordinality" clause
[03:32] <brauliobo> interesting, which sql standard it it?
[03:34] <RhodiumToad> 2008
ActiveRecord uses the sort order provided by the underlying database, which means you need to provide an "ORDER BY".
To order by ID, you would use
find(:all, :order => "id")
精彩评论