Cách chọn bản ghi cơ sở dữ liệu theo thứ tự tùy ý

Trong Rails, thật dễ dàng để lấy một loạt các bản ghi từ cơ sở dữ liệu của bạn nếu bạn có ID của chúng:

Person.where(id: [1, 2, 3]).map(&:id) => [1, 2, 3]

Nhưng điều gì sẽ xảy ra nếu bạn muốn lấy lại hồ sơ trong một khác đặt hàng? Có thể công cụ tìm kiếm của bạn trả về các ID có liên quan nhất trước tiên. Làm thế nào để bạn giữ hồ sơ của bạn theo thứ tự đó?

Bạn có thể thử where một lần nữa:

Person.where(id: [2, 1, 3]).map(&:id) => [1, 2, 3]

Nhưng điều đó hoàn toàn không hiệu quả. Vì vậy, làm thế nào làm bạn lấy lại hồ sơ theo đúng thứ tự?

Cách tương thích:case tuyên bố

Cũng giống như Ruby, SQL hỗ trợ case...when tuyên bố.

Bạn không thấy nó quá thường xuyên, nhưng case...when câu lệnh có thể hầu như hành động như băm. Bạn có thể ánh xạ giá trị này sang giá trị khác:

case :b
when :a then 1
when :b then 2
when :c then 3
end # => 2

Câu lệnh trường hợp đó giống như một hàm băm:

{
  :a => 1,
  :b => 2,
  :c => 3
}[:b] # => 2

Vì vậy, bạn có một cách để ánh xạ các khóa theo thứ tự chúng sẽ xuất hiện. Và cơ sở dữ liệu của bạn có thể sắp xếp và trả về kết quả của bạn theo thứ tự tùy ý đó.

Biết được điều đó, bạn có thể đặt các ID của mình và vị trí của chúng vào một case và sử dụng nó trong một order trong SQL mệnh đề.

Vì vậy, nếu bạn muốn các đối tượng của mình được trả về theo thứ tự [2, 1, 3], thì SQL của bạn có thể trông giống như sau:

SELECT * FROM people
  WHERE id IN (1, 2, 3)
  ORDER BY CASE id
    WHEN 2 THEN 0
    WHEN 1 THEN 1
    WHEN 3 THEN 2
    ELSE 3 END;

Bằng cách đó, hồ sơ của bạn được trả lại theo đúng thứ tự. CASE chuyển đổi mỗi ID thành thứ tự mà nó sẽ được trả lại.

Tất nhiên, điều đó trông thật nực cười. Và bạn có thể tưởng tượng một điều khoản như thế sẽ khó chịu như thế nào nếu được xây dựng bằng tay.

Nhưng bạn không có để xây dựng nó bằng tay. Đó là những gì Ruby dành cho:

module Extensions::ActiveRecord::FindByOrderedIds
  extend ActiveSupport::Concern
  module ClassMethods
    def find_ordered(ids)
      order_clause = "CASE id "
      ids.each_with_index do |id, index|
        order_clause << sanitize_sql_array(["WHEN ? THEN ? ", id, index])
      end
      order_clause << sanitize_sql_array(["ELSE ? END", ids.length])
      where(id: ids).order(order_clause)
    end
  end
end

ActiveRecord::Base.include(Extensions::ActiveRecord::FindByOrderedIds)

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

Chính xác là chúng tôi muốn nó như thế nào!

Một cách gọn gàng hơn, dành riêng cho MySQL

Nếu bạn sử dụng MySQL, có một cách dễ dàng hơn để thực hiện việc này. MySQL có ORDER BY FIELD đặc biệt cú pháp:

SELECT * FROM people
WHERE id IN (1, 2, 3)
ORDER BY FIELD(id, 2, 1, 3);

Bạn cũng có thể tạo điều đó từ Ruby:

module Extensions::ActiveRecord::FindByOrderedIds
  extend ActiveSupport::Concern
  module ClassMethods
    def find_ordered(ids)
      sanitized_id_string = ids.map {|id| connection.quote(id)}.join(",")
      where(id: ids).order("FIELD(id, #{sanitized_id_string})")
    end
  end
end

ActiveRecord::Base.include(Extensions::ActiveRecord::FindByOrderedIds)

Vì vậy, nếu bạn đang sử dụng MySQL và không quá lo lắng về khả năng tương thích, thì đây là một cách tốt. Sẽ dễ đọc hơn rất nhiều khi các câu lệnh đó lướt qua nhật ký của bạn.

Khi bạn muốn hiển thị các bản ghi theo một thứ tự cụ thể, tùy ý, bạn không cần phải sắp xếp chúng trong Ruby. Với một đoạn mã nhỏ, bạn có thể cho phép cơ sở dữ liệu làm những gì tốt của nó:tìm kiếm, sắp xếp và trả lại dữ liệu cho ứng dụng của bạn.