如何用Rails ActiveRecord实现按父类别汇总交易金额？

需要为图表库提供按父类别汇总的交易金额数据，格式示例为{"父类别1"=>金额, "父类别2"=>金额,...}。已通过纯SQL实现该需求，但希望用Rails ActiveRecord复现相同查询逻辑。目前尝试的两种查询均无法满足需求：一种只能获取父类别ID对应的金额，另一种因缺少Category表自连接而报错。

可用的纯SQL查询 #

SELECT parents.name, SUM(trxes.amount)
FROM categories as parents
JOIN categories as subcategories ON subcategories.parent_id = parents.id
JOIN trxes ON trxes.category_id = subcategories.id
GROUP BY parents.id
ORDER BY parents.id

查询结果：

[{"name"=>"Income Parent", "SUM(trxes.amount)"=>2270832},
 {"name"=>"Other", "SUM(trxes.amount)"=>1721246},
 {"name"=>"1. Everyday Expenses", "SUM(trxes.amount)"=>-14638},
 {"name"=>"2. Monthly Expenses", "SUM(trxes.amount)"=>-492363},
 {"name"=>"4. Sinking Funds", "SUM(trxes.amount)"=>59742}]

尝试的ActiveRecord查询及问题 #

1. 仅获取父类别ID对应的金额：

Category.includes(:trxes).group("categories.parent_id").sum(:amount)

结果：

{nil=>0, 1=>2270832, 2=>1721246, 3=>-14638, 4=>-492363, 5=>0, 6=>59742, 7=>0}

2. 报错的查询（提示无对应列）：

Category.includes(:trxes).group("categories.parent_id.name").sum(:amount)

生成的SQL：

SELECT SUM(amount) AS "sum_amount", categories.parent_id.name AS "categories_parent_id_name"
FROM "categories"
LEFT OUTER JOIN "trxes" ON "trxes"."category_id" = "categories"."id"
GROUP BY categories.parent_id.name

错误信息：

SQLite3::SQLException: no such column: categories.parent_id.name (ActiveRecord::StatementInvalid)

模型定义 #

class Category < ApplicationRecord
  belongs_to :parent, class_name: "Category", optional: true
  has_many :subcategories, class_name: "Category", foreign_key: :parent_id
  has_many :trxes
end

class Trx < ApplicationRecord
  belongs_to :category, optional: true
end

数据库Schema #

create_table "categories", force: :cascade do |t|
  t.string "name", null: false
  t.integer "parent_id"
  # 其他字段省略
end

create_table "trxes", force: :cascade do |t|
  t.integer "amount", default: 0, null: false
  t.integer "account_id"
  t.integer "category_id"
  t.index ["category_id"], name: "index_trxes_on_category_id"
  # 其他字段省略
end

正确的ActiveRecord查询写法 #

要复现原SQL的自连接逻辑，需要显式关联父类别和子类别，再关联交易表，最后分组求和：

写法一：利用模型关联构建查询 #

# 通过subcategories关联子类别，再关联trxes，按父类别名称分组求和
Category.joins(subcategories: :trxes)
        .group('categories.name')
        .sum('trxes.amount')

该查询会直接返回{"父类别名称"=>金额,...}格式的结果，与需求示例一致。

写法二：贴近原SQL的别名写法 #

如果需要更贴近原SQL的别名逻辑，可以显式指定表别名：

Category.from('categories as parents')
        .joins('join categories as subcategories on subcategories.parent_id = parents.id')
        .joins('join trxes on trxes.category_id = subcategories.id')
        .group('parents.name')
        .sum('trxes.amount')

补充说明 #

两种写法都会生成与原SQL逻辑一致的查询语句，自动过滤无交易数据的父类别；如果需要保留金额为0的父类别，可将joins替换为left_joins。

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