Preserving the order of an array when using .where in Rails

Question

I am passing an array of ids to a .where in Rails, but the way it's returned doesn't preserve the order. For example, here's the array:

2.5.1 :043 > company_ids
 => [83, 79, 52, 44, 82]

I am looking for all pages that have those company IDs, but returned in the order of those company IDs that were provided. This is the result if I try to compare:

2.5.1 :044 > Page.where(company_id: company_ids).pluck(:company_id)
   (1.1ms)  SELECT "pages"."company_id" FROM "pages" WHERE "pages"."company_id" IN ($1, $2, $3, $4, $5)  [["company_id", 83], ["company_id", 79], ["company_id", 52], ["company_id", 44], ["company_id", 82]]
 => [83, 82, 52, 44, 79]

I ran across this stackoverflow post (ActiveRecord.find(array_of_ids), preserving order) that seems to provide a solution, but it doesn't work for me. When trying to use Page.where(company_id: company_ids).order("field(company_id, #{company_ids.join ','})") as suggested in the stackoverflow post, I get the following error:

2.5.1 :042 > Page.where(company_id: company_ids).order("field(company_id, #{company_ids.join ','})")
  Page Load (2.0ms)  SELECT  "pages".* FROM "pages" WHERE "pages"."company_id" IN ($1, $2, $3, $4, $5) ORDER BY field(company_id, 83,79,52,44,82) LIMIT $6  [["company_id", 83], ["company_id", 79], ["company_id", 52], ["company_id", 44], ["company_id", 82], ["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR:  function field(bigint, integer, integer, integer, integer, integer) does not exist)
LINE 1: ...ts"."company_id" IN ($1, $2, $3, $4, $5) ORDER BY field(comp...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
: SELECT  "pages".* FROM "pages" WHERE "pages"."company_id" IN ($1, $2, $3, $4, $5) ORDER BY field(company_id, 83,79,52,44,82) LIMIT $6

I just simply want to call Pages.where(company_id: company_ids) and get the pages back based on order of company_ids that was provided.

As a workaround, I am using this:

company_ids = companies.order("full_name ASC").pluck(:id)
pages = []
company_ids.each {|c| pages << Page.find_by(company_id: c)}

but seems like that would be inefficient if there are thousands of records. or even just hundreds.

Answer 1

The problem is you're using a MySQL function (field), but the RDBMS you're using is PostgreSQL.

You can try with position, using the row company_id and the array of ids concatenated:

Page.where(company_id: company_ids).order("position(company_id::text in '#{ids.join(',')}')")

Use:

Page.where(company_id: company_ids).order(Arel.sql("position(company_id::text in '#{ids.join(',')}')"))

if you're getting a deprecation warning.

There also exists the possibility to use find, which as stated in the doc says The returned records are in the same order as the ids you provide. Although I don't know if it applies for your case.