How to fetch all parent records on a self-join model

Question

Suppose we have the following setup:

class Category < ApplicationRecord
  belongs_to :parent, class_name: 'Category', foreign_key: :parent_id
  has_many :children, class_name: 'Category', foreign_key: :parent_id
end

In other words: A category can have sub-categories. (And if the category has no parent, then it's a "top-level" category.)

What I'd like is: Given a collection of categories, merge this with their parent categories.

If it makes things easier, for now it's safe to assume that each category will be at most "one level deep", i.e. there are no "grand-child" categories. (But this might change one day, so ideally I'd like the code to be robust against that!)

My use case is that I'd like to be able to do something like this:

post.categories = categories.with_parents

..Since that will make all of the existing parent-specific category logic simpler to handle going forwards.

Naturally I could handle this by doing something like:

post.categories = (categories + categories.map(&:parent)).compact.uniq

...but I'm hoping there's a way to achieve this more elegantly and efficiently.

Here's the best way I've found so far, but it feels... wrong 😬 -- surely there a way of doing this with a join/union/merge??

class Category < ApplicationRecord
  # ...

  def self.with_parents
    self.or(unscoped.where(id: select(:parent_id)))
  end
end

Answer 1

If you just want to assign category and parent to post, this is as simple as I could make it:

def self.with_parent_ids
  pluck(:id, :parent_id).flatten.compact.uniq
end

>> categories = Category.limit(2).reverse_order.with_parent_ids
  Category Pluck (0.8ms)  SELECT "categories"."id", "categories"."parent_id" FROM "categories" ORDER BY "categories"."id" DESC LIMIT $1  [["LIMIT", 2]]
=> [6, 3, 5, 2]
>> Post.first.category_ids = categories

---

I don't know if this is more elegant, but if parent_id is the only thing you have and you want to get all the parents up the tree:

>> Category.find_by_sql <<~SQL
  WITH RECURSIVE tree AS (
    ( #{Category.where(id: 5).to_sql} )
  UNION
    ( SELECT "categories".* FROM "categories" JOIN tree ON categories.id = tree.parent_id )
  ) SELECT * FROM tree
SQL

=>
[#<Category:0x00007f7fe354aa78 id: 5, name: "Audiobooks", parent_id: 2>, # started here
 #<Category:0x00007f7fe354a9b0 id: 2, name: "Books", parent_id: 1>,      # parent
 #<Category:0x00007f7fe354a8e8 id: 1, name: "Products", parent_id: nil>] # grand parent
                                                                         # etc