ActiveRecord condition with order based on ad-hoc order of attributes

Question

Working with an array

@valid_ts = [669, 668, 667, 34, 35, 36, 37, 38, 39, 40, 41, 61, 62, 63, 64, 65, 66, 130, 131, 132, 133, 134, 135, 136, 137, 157, 158, 159, 160, 161, 162, 163, 164]
=> [669, 668, 667, 34, 35, 36, 37, 38, 39, 40, 41, 61, 62, 63, 64, 65, 66, 130, 131, 132, 133, 134, 135, 136, 137, 157, 158, 159, 160, 161, 162, 163, 164]

slots = Timeslot.where('id IN (?)', @valid_ts).all
[34, 35, 36, 37, 38, 39, 40, 41, 61, 62, 63, 64, 65, 66, 130, 131, 132, 133, 134, 135, 136, 137, 157, 158, 159, 160, 161, 162, 163, 164, 667, 668, 669]

Rails is running the query based on its default attribute updated_at, whereas the goal is to maintain the results in the order of the provided array.

slots = Timeslot.where('id IN (?)', @valid_ts).sort_by { |valid_ts| @valid_ts.index valid_ts }.pluck('id')
[669, 35, 36, 37, 38, 39, 40, 41, 61, 62, 63, 64, 65, 66, 130, 131, 132, 133, 134, 135, 136, 137, 157, 158, 159, 160, 161, 162, 163, 164, 667, 668, 34]

fails, only inverting first and last elements of array. Based on this discussion, with answer by Ajedi32 proper direction is provided...

slots = Timeslot.find(@valid_ts).sort_by { |valid_ts| @valid_ts.index valid_ts }.pluck('id')
Timeslot Load (0.7ms)  SELECT "timeslots".* FROM "timeslots" WHERE "timeslots"."id" 
   IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33)  
   [["id", 669], ["id", 668], ["id", 667], ["id", 34], ["id", 35], ["id", 36], ["id", 37], ["id", 38], ["id", 39], ["id", 40], ["id", 41], ["id", 61], ["id", 62], ["id", 63], ["id", 64], ["id", 65], ["id", 66], ["id", 130], ["id", 131], ["id", 132], ["id", 133], ["id", 134], ["id", 135], ["id", 136], ["id", 137], ["id", 157], ["id", 158], ["id", 159], ["id", 160], ["id", 161], ["id", 162], ["id", 163], ["id", 164]]

seems to query properly, however, as in the above case the returned array has the index bumped off by one (i.e. last element is put at head of queue)

[164, 668, 667, 34, 35, 36, 37, 38, 39, 40, 41, 61, 62, 63, 64, 65, 66, 130, 131, 132, 133, 134, 135, 136, 137, 157, 158, 159, 160, 161, 162, 163, 669]

How can this array be properly sorted to reflect the submitted array?

`Timeslot.find(@valid_ts)` should be enough. From the docs for [`find`](https://api.rubyonrails.org/classes/ActiveRecord/FinderMethods.html#method-i-find): _"The returned records are in the same order as the ids you provide."_ — Stefan, Mar 29 '20 at 10:06
@Stefan. I understand it should be enough, and the SQL is loading in that way apparently. Why then is the last item of the array being sent to the front of the queue? — Jerome, Mar 29 '20 at 10:49
Your example either use `where` or an additional `sort_by`. Try removing that and only use `find`. — Stefan, Mar 29 '20 at 12:13

score 2 · Accepted Answer · answered Mar 29 '20 at 10:32

Justin Weiss wrote in his blog about this problem.

It is a good approach to tell the database about the preferred order and load all records sorted in that order directly from the database. Example from his blog article:

# in config/initializers/find_by_ordered_ids.rb
module FindByOrderedIdsActiveRecordExtension
  extend ActiveSupport::Concern
  module ClassMethods
    def find_ordered(ids)
      order_clause = "CASE id "
      ids.each_with_index do |id, index|
        order_clause << "WHEN #{id} THEN #{index} "
      end
      order_clause << "ELSE #{ids.length} END"
      where(id: ids).order(order_clause)
    end
  end
end

ActiveRecord::Base.include(FindByOrderedIdsActiveRecordExtension)

That allows you to write:

Object.find_ordered([2, 1, 3]) # => [2, 1, 3]

This is a valid answer for Rails < 5.2 (certainly up to 4.2). As of 5.2, `find`does do the job. — Jerome, Apr 21 '20 at 08:08

score 1 · Answer 2 · answered Mar 29 '20 at 12:43

If you are using Mysql you can use field function.

Timeslot.where(id: @valid_ts).order("field(id, #{ids.join ','})")

If you are using Postgres you can use position function. -

Timeslot.where(id: @valid_ts).order("position(id::text in '#{ids.join(',')}')")

But ActiveRecord can perform for both if you are using rails >= 5.2.0 as it is added from that version. Most likely it is also backported in rails 5.0. The pull request and the commit and the documentation.

Timeslot.find(@valid_ts)

From the documentation of find -

find(*args)
Find by id - This can either be a specific id (1), a list of ids (1, 5, 6), or an array of ids ([5, 6, 10]). If one or more records cannot be found for the requested ids, then ActiveRecord::RecordNotFound will be raised. If the primary key is an integer, find by id coerces its arguments by using to_i.

Person.find(1)          # returns the object for ID = 1
Person.find("1")        # returns the object for ID = 1
Person.find("31-sarah") # returns the object for ID = 31
Person.find(1, 2, 6)    # returns an array for objects with IDs in (1, 2, 6)
Person.find([7, 17])    # returns an array for objects with IDs in (7, 17)
Person.find([1])        # returns an array for the object with ID = 1
Person.where("administrator = 1").order("created_on DESC").find(1)
NOTE: The returned records are in the same order as the ids you provide. If you want the results to be sorted by database, you can use ActiveRecord::QueryMethods#where method and provide an explicit ActiveRecord::QueryMethods#order option. But ActiveRecord::QueryMethods#where method doesn't raise ActiveRecord::RecordNotFound.

Check this SO post for more ideas.

ActiveRecord condition with order based on ad-hoc order of attributes

2 Answers2

Linked