Rails ActiveRecord 自连接表查询父子分类名称实现方案

• 查询所有存在父级的分类记录，结果需同时展示父分类名称与当前子分类名称
• 项目基于Rails框架实现，分类表categories为自关联结构：分类可作为其他分类的父级，构成树形层级关系

可用的原生SQL写法 #

直接执行原生SQL可以得到符合预期的结果：

> sql="select p.name parent, s.name category from categories s join categories p on s.parent_id=p.id"
> ActiveRecord::Base.connection.execute(sql)

   (0.4ms)  select p.name parent, s.name category from categories s join categories p on s.parent_id=p.id
=>
[{"parent"=>"Income", "category"=>"Available next month"},
 {"parent"=>"Income", "category"=>"Available this month"},
 {"parent"=>"1. Everyday Expenses", "category"=>"Fuel"},
 {"parent"=>"1. Everyday Expenses", "category"=>"Groceries"},
 {"parent"=>"1. Everyday Expenses", "category"=>"Restaurants"},
 {"parent"=>"1. Everyday Expenses", "category"=>"Entertainment"},
 {"parent"=>"1. Everyday Expenses", "category"=>"Household & Cleaning"},
 {"parent"=>"1. Everyday Expenses", "category"=>"Clothing"},
 {"parent"=>"1. Everyday Expenses", "category"=>"MISC"},
 {"parent"=>"2. Monthly Expenses", "category"=>"Phone"},
 {"parent"=>"2. Monthly Expenses", "category"=>"Rent"},
 {"parent"=>"2. Monthly Expenses", "category"=>"Internet & Utilities"},
 {"parent"=>"2. Monthly Expenses", "category"=>"News Subscriptions"},
 {"parent"=>"2. Monthly Expenses", "category"=>"Car Registration"}]

已尝试的无效写法 #

写法1：select别名返回Category实例，无法直接取属性 #

生成的SQL和目标SQL几乎一致，但返回的是Category实例对象，自定义别名的属性没有直接暴露，无法直接使用：

> Category.select('parents_categories.name as parent, categories.name as category').joins(:parent)

  Category Load (0.7ms)  SELECT parents_categories.name as parent, categories.name as category FROM "categories" INNER JOIN "categories" "parents_categories" ON "parents_categories"."id" = "categories"."parent_id"                                                                                                                   
=>                                                                                                                                                
[#<Category:0x000055fefee12af0 id: nil>,                                                                                                          
 #<Category:0x000055fefee12a28 id: nil>,                                                                                                          
 #<Category:0x000055fefee12960 id: nil>,                                                                                                          
 #<Category:0x000055fefee12898 id: nil>, 
...

写法2：select语法错误，无法读取父分类字段 #

传参语法有误，最终生成的SQL只查询了子分类的name字段，拿不到父分类名称：

> Category.select(:parent['name'],:name).joins(:parent).first

  Category Load (0.2ms)  SELECT "categories"."name" FROM "categories" INNER JOIN "categories" "parents_categories" ON "parents_categories"."id" = "categories"."parent_id" ORDER BY "categories"."id" ASC LIMIT ?  [["LIMIT", 1]]                                                            
=> #<Category:0x000055feff38cd78 id: nil, name: "Available next month">

写法3：循环遍历组装，逻辑冗余 #

先预加载关联再遍历拼接哈希可以得到结果，但写法繁琐，不是最优解：

> c = Category.where.not(parent: nil).includes(:parent)
> c_data = [ ] 
> c.each do |c|
    c_data << {
       parent: c.parent.name,
       category: c.name
    }
end

[{:parent=>"Income", :category=>"Available next month"},
 {:parent=>"Income", :category=>"Available this month"},
 {:parent=>"1. Everyday Expenses", :category=>"Fuel"},
 {:parent=>"1. Everyday Expenses", :category=>"Groceries"},
 {:parent=>"1. Everyday Expenses", :category=>"Restaurants"},...]

表结构 #

create_table "categories", force: :cascade do |t|
  t.string "name", null: false
  t.integer "parent_id"
  ...
end

模型关联定义 #

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

第一种写法的SQL逻辑完全正确。ActiveRecord默认返回模型实例，自定义select的别名属性可以直接通过实例方法调用，无需额外遍历组装：

# 生成SQL与原生SQL完全一致，INNER JOIN自动过滤无父级的根分类
category_records = Category.select('parents_categories.name as parent_name, categories.name as category_name')
                           .joins(:parent)

# 直接读取别名属性组装结果
result = category_records.map { |item| { parent: item.parent_name, category: item.category_name } }

如果追求更高性能，可以直接用pluck跳过模型实例化环节，直接拿到字段值组装结果：

result = Category.joins(:parent)
                 .pluck('parents_categories.name', 'categories.name')
                 .map { |parent_name, category_name| { parent: parent_name, category: category_name } }

两种写法都不需要额外加where.not(parent_id: nil)条件，INNER JOIN本身就会自动排除parent_id为空、或父分类不存在的记录，生成的SQL和手写原生SQL逻辑完全一致，比预加载后遍历的写法执行效率更高、代码更简洁。

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