How would one go about eager loading a categories collection's root categories?

[pugsiman]

Say I have a collection of categories, how would I go about eager loading all of their root categories (unless they're the root categories themselves) in one query?

[fizvlad]

(The question is 4 years, yeeeeah 🐧)

While solving similar problem, I've ended up using association through scenic view

I have created an SQL view which looked something like this:

SELECT
    DISTINCT ON (descendant_id)
    ancestor_id AS root_id,
    descendant_id AS leaf_id,
    generations AS depth
FROM category_hierarchies
ORDER BY descendant_id, generations DESC

Then I defined a read-only model which uses this view:

# frozen_string_literal: true

class CategoryRootHierarchy < ApplicationRecord
  self.primary_key = nil

  belongs_to :leaf, class_name: 'Category'
  belongs_to :root, class_name: 'Category'

  def readonly?
    true
  end
end

... and added an association to category model:

# frozen_string_literal: true
class Category < ApplicationRecord
  has_closure_tree order: :code, name_column: :code, dependent: :destroy

  has_many :items, dependent: :nullify
  has_one :root_hierarchy, class_name: 'CategoryRootHierarchy', foreign_key: 'leaf_id', inverse_of: :leaf
end

After that I was able to preload the root category:

Item.includes(category: [{ root_hierarchy: [:root] }])

---

This is probably can be done on Rails level with use of ancestor_hierarchies association, but it will probably look scarier