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

Question

Hi,

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

Answer 1

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)