How to order collection by ActiveStorage attachment name?

Question

I need to order a collection by 2 attributes. One is a regular attribute, I'll call it attr and the other is the name of an ActiveStorage attachment. The problem is that because of how ActiveStorage defines the relationship I can't join its table to order by one of its attributes.

This is roughly what I'm trying to do:

class Document < ApplicationRecord
  has_one_attached :file

  scope :sorted, -> { joins(:file).order(attr: :asc, 'files.filename': :asc) }
end

I know that files isn't the name of the table, this is just an example. I've messed around ActiveStorage's table name and even tried defining a relationship manually with ActiveStorage::Attachment and ActiveStorage::Blob without success.

Any ideas about how to achieve this?

Answer 1

To order the documents on the DB server, you can join through attachments to blobs:

class Document < ApplicationRecord
  has_one_attached :file
  scope :ordered_by_filename, -> { joins(file_attachment: :blob).order("active_storage_blobs.filename ASC") }
end

Alternatively, denormalize for performance. Keep a copy of the filename in the documents table:

class Document < ApplicationRecord
  has_one_attached :file
  before_save { self.filename ||= file.filename }
  scope :ordered_by_filename, -> { order(filename: :asc) }
end

Finally, consider sorting in the app wherever feasible. (Pagination is one case where it’s probably not feasible.) Where scalability is a concern, it’s generally easier to scale your app server pool horizontally than your DB:

class Document < ApplicationRecord
  has_one_attached :file
  delegate :filename, to: :file

  def self.ordered_by_filename
    # Use the with_attached_files scope to avoid N+1 queries.
    with_attached_files.sort_by(&:filename)
  end
end