如何用Rails原生方式实现两表联合查询并按不同命名列排序？

方案一：利用Rails 6+的union方法（推荐） #

通过ActiveRecord链式调用构建两个模型的查询，统一排序字段别名后执行联合查询，再处理排序和分页：

# 构建Books查询，指定统一的字段别名
books_query = Book.where(account_id: user.account.id)
                  .select("'books' AS record_type", :id, "transaction_date AS sort_date")

# 构建Spree::Payments查询，关联orders和stores并指定字段别名
payments_query = Spree::Payment.joins(order: :store)
                              .where(spree_stores: { account_id: user.account.id })
                              .select("'spree_payments' AS record_type", :id, "created_at AS sort_date")

# 联合查询、排序、分页
combined_records = books_query.union(payments_query)
                              .order(sort_date: :desc)
                              .limit(25)
                              .offset(26)

方案二：兼容低版本Rails的AREL实现 #

如果你的Rails版本低于6，不支持原生union方法，可以用AREL手动构建联合查询：

# 构建Books的AREL查询
books_arel = Book.where(account_id: user.account.id)
                 .select(Arel.sql("'books' AS record_type"), Book.arel_table[:id], Book.arel_table[:transaction_date].as('sort_date'))
                 .arel

# 构建Spree::Payments的AREL查询
payments_arel = Spree::Payment.joins(order: :store)
                              .where(spree_stores: { account_id: user.account.id })
                              .select(Arel.sql("'spree_payments' AS record_type"), Spree::Payment.arel_table[:id], Spree::Payment.arel_table[:created_at].as('sort_date'))
                              .arel

# 执行联合查询并处理排序分页
combined_records = ActiveRecord::Base.from(books_arel.union(payments_arel), :combined_records)
                                     .order(sort_date: :desc)
                                     .limit(25)
                                     .offset(26)

优化建议：封装模型类方法 #

把查询逻辑封装到对应模型的类方法中，提升代码可读性和复用性：

# app/models/book.rb
class Book < ApplicationRecord
  def self.for_account(account_id)
    where(account_id: account_id)
      .select("'books' AS record_type", :id, "transaction_date AS sort_date")
  end
end

# app/models/spree/payment.rb
module Spree
  class Payment < ApplicationRecord
    def self.for_account(account_id)
      joins(order: :store)
        .where(spree_stores: { account_id: account_id })
        .select("'spree_payments' AS record_type", :id, "created_at AS sort_date")
    end
  end
end

# 业务逻辑中调用
books = Book.for_account(user.account.id)
payments = Spree::Payment.for_account(user.account.id)
combined_records = books.union(payments).order(sort_date: :desc).limit(25).offset(26)

方案优势 #

• 完全基于ActiveRecord语法，符合Rails原生风格，避免硬写大段SQL
• 用sort_date统一排序字段别名，替代原SQL中按索引排序的写法，避免字段顺序变动引发的错误
• 链式调用和模型封装让代码更易维护、重构和测试

内容的提问来源于stack exchange，提问作者Pavle Kostovic