Finding an array of ids while keeping the order with Rails and PostGreSQL

Question

I have an array of ids of objects that I want to get from the database, but PostGreSQL returns them sorted by ids:

Users.find([4, 1, 3])
=> [User 1, User 3, User 4]

I know I could sort them back like this:

ids = [4, 3, 1]
r = Users.find(ids)
users = ids.map{|id| r.detect{|each| each.id == id}}

But wouldn't it be better if I could do this with the database? I know MySQL has a "field" option. Does PostGreSQL has something equivalent?

Thank you,

Kevin

FYI, PostgreSQL does NOT "return them sorted by ids". Either your ORM is sorting it or it just happened to come back in that order. An unordered select from the database is not guaranteed to come back in any order and assuming it does will ultimately lead to odd behavior. — Matthew Wood
– Matthew Wood, Commented Oct 26, 2010 at 18:21

mnencia · Accepted Answer · 2010-10-26 09:11:52Z

2

This function will simulate the one from MySQL

CREATE OR REPLACE FUNCTION field(anyelement, anyarray) RETURNS integer AS 
$body$
  SELECT COALESCE((
    SELECT i
    FROM generate_series(array_lower($2, 1), array_upper($2, 1)) s(i)
    WHERE $2[i] = $1
  ),0);
$body$ LANGUAGE SQL STABLE;

Example of usage:

CREATE TABLE x (id integer);
INSERT INTO x (1),(2),(3);
SELECT * FROM x ORDER BY field(id, ARRAY[3,1,2]);
 id 
----
  3
  1
  2
(3 rows)

answered Oct 26, 2010 at 9:11

mnencia

3,3881 gold badge26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Finding an array of ids while keeping the order with Rails and PostGreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related