Rails 7.1 adds support for multiple-column ordering in Activerecord

PostgreSQL supports ordering by multiple columns using the ORDER BY clause. The ORDER BY clause allows you to specify a list of columns to sort by, and you can optionally specify whether to sort each column in ascending or descending order.

SELECT *
FROM authors
ORDER BY last_name ASC, first_name DESC;

This query will sort the authors table by the last_name column in ascending order and then by the first_name column in descending order.

Rails 7.1 adds the above support for specifying the sorting order for each key within a composite primary key when using ActiveRecord::Batches methods for models.

Before Rails 7.1

Let's assume a Rails application which has an Author model. For simplicity, let's say the primary key is set to first_name and last_name as shown below:

class Author < ApplicationRecord
  self.primary_key = [:first_name, :last_name]
end

Prior to Rails 7.1, when handling batches of records with a composite primary key, such as first_name and last_name, developers could utilize the :asc or :desc argument to regulate the sorting order. However, this sorting mechanism had a restriction. Employing :asc or :desc to define the sorting order affected both first_name and last_name collectively. This meant that requesting ascending order sorted both first_name and last_name in ascending order together, and requesting descending order sorted both columns in descending order together.

You can execute the batch query on the Author model as below:

Author.find_each(order: [:first_name, :last_name]).each do |author|
  # your code
end

Trying to pass an array [:desc, :asc] to the order clause raises an ArgumentError.

Author.find_each(order: [:desc, :asc]).each do |author|
  # your code
end

:order must be :asc or :desc, got [:desc, :asc] (ArgumentError)

In Rails 7.1

Rails 7.1 adds support for multiple-column ordering in Activerecord. You can now select the sorting order for each key within a composite primary key.

class Author < ApplicationRecord
  self.primary_key = [:first_name, :last_name]
end

Author.find_each(order: [:desc, :asc]).each do |author|
  # your code
end

The above code works without raising any argument error. It sorts the list of authors in descending order by first_name and then sorts the result by last_name in ascending order.

The ability to specify sorting orders for composite primary keys extends beyond the find_each method and applies to other batch processing methods offered by ActiveRecord::Batches, namely find_in_batches and in_batches. These methods enable efficient retrieval and processing of records in batches, similar to find_each.

To know more about this feature, please refer to this PR.