I've been testing elasticsearch in a rails project recently - and stumbled on an issue with sorting.

You see - elasticsearch returns the search results either sorted by score or by the sort order you ask for. Then you need to convert the search results to records - so a simple

ModelType.where(id: ids)

However - on postgresql - this returns the records in database order (seems to be insert order).

You could try sorting post fetch:

index = ModelType.where(id: ids).to_a.group_by(&:id)
ids.map { |i| index[i.to_i].first }

But - what if we want to do this in the database.

A google search led me to this article on how to give postgresql a similar function to mysql's find_in_set

So - adding a rails migration to add the function:

create or replace function find_in_array(
  needle anyelement, haystack anyarray) returns integer as $$
declare
  i integer;
begin
  for i in 1..array_upper(haystack, 1) loop
    if haystack[i] = needle then
      return i;
    end if;
  end loop;
  raise exception 'find_in_array: % not found in %', needle, haystack;
end;
$$ language 'plpgsql';

and an initializer file where we override:

class String
  def sql_escape
    self.gsub(/[%_'\\"]/, "\\\\\\0")
  end
end

class Array
  def to_postgres_array
    "'{" + self.inject([]) do |mem, val|
      mem << (val.kind_of?(String) ? "\"#{val.sql_escape}\"" : val)
      mem
    end.join(", ") + "}'"
  end
end

We end up able to call:

ModelType.where(id: ids).order("find_in_array(id, #{ids.to_postgres_array})")

Benchmarking this on my small'ish dataset shows that this is faster than the post fetch sort in ruby. Not sure what it does on larger data sets though.