Rails ActiveRecord:列出自连接表中的父类别和子类别

Question

我想返回如下所示的列表。具有父类的所有类别记录。显示父名称和类别名称。

我正在使用一个自连接(自关联?)模型'category'，其中的类别可以是其他类别的父类。

它的工作原理与纯SQL下面，但我怎么能做到与ActiveRecord?

> 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"}]

我一直在尝试几个查询。这似乎复制了我最接近的SQL，但没有返回任何可用的。

> 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>, 
...

这是我的另一个尝试，但我与语法斗争，它只是忽略:parent['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">

模式

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

我在Rails指南中找不到一个匹配的例子:https://guides.rubyonrails.org/active_record_querying.html像这样的stackoverflow问题(无法在Rails中加入自连接表)很接近，但没有让我越过终点线

更新:我从这个网站找到了一个令人费解的答案:https://medium.com/@swapnilggourshete/rails-incls-vss-joins-9bf3a8ada00

> 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"},...]

但一定有更好的办法。

Answer 1

你真是我的好朋友。只要稍微调整一下，你就会得到你想要的

Category.select('parents_categories.name as parent_category, categories.name as category').joins(:parent).as_json(except: :id)

注意，如果您有belongs_to :parent,parent不能被命名为选定键，所以我们需要将其更改为parent_category