How to Select Database Records in an Arbitrary Order

In Rails, it’s easy to get a bunch of records from your database if you have their IDs:

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

But what if you wanted to get the records back in a different order? Maybe your search engine returns the most relevant IDs first. How do you keep your records in that order?

You could try where again:

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

But that doesn’t work at all. So how do you get your records back in the right order?

The compatible way: case statements

Just like Ruby, SQL supports case...when statements.

You don’t see it too often, but case...when statements can almost act like hashes. You can map one value to another:

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

That case statement kind of looks like a hash:

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

So, you have a way to map keys to order they should appear in. And your database can sort and return your results by that arbitrary order.

Knowing that, you could put your IDs and their position into a case statement, and use it in a SQL order clause.

So if you wanted your objects returned in the order [2, 1, 3], your SQL could look like this:

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;

That way, your records are returned in the right order. The CASE transforms each ID into the order it should be returned in.

Of course, that looks ridiculous. And you could imagine how annoying a clause like that would be to build by hand.

But you don’t have to build it by hand. That’s what Ruby’s for:

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]

Exactly how we wanted it!

A cleaner, MySQL-specific way

If you use MySQL, there’s a cleaner way to do this. MySQL has special ORDER BY FIELD syntax:

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

You could also generate that from 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)

So, if you’re using MySQL, and not too worried about compatibility, this is a good way to go. It’s a lot easier to read as those statements fly through your logs.

When you want to display records in a specific, arbitrary order, you don’t need to sort them in Ruby. With a little code snippet, you can let the database do what it’s good at: finding, sorting, and returning data to your app.