Model has_many AND has_many :through in one?

Question

I have a set of categories, belonging to a category set. These categories can themselves have a mix of categories (self-referential) and also questions. The models and their relationships are defined & visualized as follows:

class CategorySet < ActiveRecord::Base
  has_many :categories
end

class Category < ActiveRecord::Base
  belongs_to :category_set

  belongs_to :parent_category, :class_name => "Category"
  has_many :categories, :class_name => "Category", :foreign_key => "parent_category_id", dependent: :destroy

  has_many :questions, dependent: :destroy
end

class Question < ActiveRecord::Base
  belongs_to :category
end

Abbrev to CS, C and Q:

CS
  |- C
     |- Q
     |
     |- C
     |
     |- C
     |  |- Q
     |
     |- C
        |- Q
        |- Q

I would like to be able to ask CategorySet.find(1).questions and return all questions in the tree regardless of position. The only ways I can think of use lots of function-based requests and will probably be overkill on sql statements (see below for an example).

Calling CategorySet.find(1).categories finds only the direct descendant categories of the category set. Also, Category.find(id).questions returns only the questions for that category.

I have tried overwriting the .questions method on categories, but that doesn't seem very rails relationship-esque and there must be a better way of doing this? Alo it means I can't do the CategorySet.includes(:questions).all style syntax which greatly reduces the load on the database server

Answer 1

Approach 1

Use awesome_nested_set for this

class CategorySet < ActiveRecord::Base
  has_many :categories
  def questions
    categories.map do |c|   
      c.self_and_descendants.include(:questions).map(&:questions)
    end.flatten
  end
end

class Category < ActiveRecord::Base
  awesome_nested_set
  belongs_to :category_set
  has_many :questions
end

class Question < ActiveRecord::Base
  belongs_to :category
end

Refer to the awesome_nested_set documentation to get the list of additional columns required by the gem.

Approach 2

The approach 1 loads all the questions in to the memory and it does not support DB based pagination.

You can get better performance if you avoid maintaining a separate table for CategorySet as each category can contain other categories.

class Category < ActiveRecord::Base
  awesome_nested_set
  has_many :questions
  # add a boolean column called category_set

  def questions
    join_sql = self_and_descendants.select(:id).to_sql
    Question.joins("JOIN (#{join_sql}) x ON x.id = questions.id")  
  end
end

class Question < ActiveRecord::Base
  belongs_to :category
  validates :category_id, :presence => true, :category_set => true
end

# lib/category_set_validator.rb
class CategorySetValidator < ActiveModel::EachValidator  
  def validate_each(object, attribute, value)  
    if record.category.present? and record.category.category_set?
      record.errors[attribute] << (options[:message] || 
                    "is pointing to a category set") 
    end
  end 
end  

Now you can get questions for a category set as

cs.questions.limit(10).offset(0)
cs.questions.paginate(:page => params[:page])  # if you are using will_paginate