Rails ActiveRecord: List parent category and sub category from self-join table-CodePudding

I want to return a list like the one below. All category records which have a parent. Displaying parent name and category name.

I'm using a self-join (self-associated?) model 'category' in which categories can be parents of other categories.

It works with the pure SQL just below, but how can I do it with 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"}]

I've been trying several queries. This appears to replicate my SQL closest, but returns nothing usable.

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

This was my other try, but I'm struggling with the syntax, and it just ignores the :parent['name'] phrase

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

Schema

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

Model

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

I can't find a matching example from the Rails guide here: https://guides.rubyonrails.org/active_record_querying.html And stackoverflow questions like this (Unable to join self-joins tables in Rails) are close but not getting me across the finish line

UPDATE: I've found a convoluted answer from this website: https://medium.com/@swapnilggourshete/rails-includes-vs-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"},...]

But there must be a better way.

CodePudding user response：

You are really close my friend. Just a little tweak and you will get what you are looking for

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

Note if you have belongs_to :parent, parent cannot be named as selected key so we need to change it to parent_category