Rails ActivereCord：列出自加入表中的父级类别和子类别

Question

我想返回下面的清单。所有具有父母的类别记录。显示父姓名和类别名称。

我正在使用自加入（自我相关？）模型“类别”，其中哪些类别可以成为其他类别的父母。

它可以与下面的纯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_record_querying.html 和stackoverflow这样的问题（ /a>）很接近，但没有让我跨越终点线

更新：我从此网站找到了一个令人费解的答案： https://medium.com/@swapnilggourshete/rails-includes-vs-vs-joins-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

你真的很亲密。只需一点调整，如果您拥有allats_to：parent，parent无法将其命名为选定的密钥，则您将获得您要注意的内容

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

，因此我们需要将其更改为<代码> parent_category