CODEWITHSUNDEEP
ruby-on-railsruby
Jerome
Jerome

Reputation: 6189

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

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?

Upvotes: 0

Views: 213

Answers (2)

Rafayet Monon
Rafayet Monon

Reputation: 1179

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.

Upvotes: 1

spickermann
spickermann

Reputation: 106792

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]

Upvotes: 2

Related Questions