CODEWITHSUNDEEP
ruby-on-rails
FabKremer
FabKremer

Reputation: 2169

Get collections from all the child objects in rails

I have implemented something like this: 

class Move < ActiveRecord::Base
  has_many :move_categories
  has_many :categories, through: :move_categories
end

class Category < ActiveRecord::Base
  has_many :move_categories
  has_many :moves, through: :move_categories
  belongs_to :parent, class_name: 'Category'
  has_many :subcategories, class_name: 'Category', foreign_key: 'parent_id'
end

So for a given a Category, I need to find the best way to get all my moves, and also all the ones from my sub-categories, and the ones from the sub-categories of my sub-categories, and so on.

Any thoughts?

EDIT:

Here's the MoveCategory model:

class MoveCategory < ActiveRecord::Base
  belongs_to :move
  belongs_to :category

  validates_uniqueness_of :category_id, scope: :move_id
end

Upvotes: 2

Views: 383

Answers (1)

PinnyM
PinnyM

Reputation: 35533

Since you are using PostreSQL, you should take advantage of the WITH RECURSIVE CTE syntax to do this. See http://hashrocket.com/blog/posts/recursive-sql-in-activerecord for a great writeup.

If you implement the tree methods, all that's missing is the Move retrieval:

def Category < ActiveRecord::Base
  def descendant_moves
    tree_id_sql = self.class.tree_sql_for(self)
    Move.joins(:move_categories).where("move_categories.category_id IN (#{tree_id_sql})")
  end
end

For completeness, here's the code for the tree_sql_for method from the above listed page:

def self.tree_sql_for(instance)
  tree_sql =  <<-SQL
    WITH RECURSIVE search_tree(id, path) AS (
        SELECT id, ARRAY[id]
        FROM #{table_name}
        WHERE id = #{instance.id}
      UNION ALL
        SELECT #{table_name}.id, path || #{table_name}.id
        FROM search_tree
        JOIN #{table_name} ON #{table_name}.parent_id = search_tree.id
        WHERE NOT #{table_name}.id = ANY(path)
    )
    SELECT id FROM search_tree ORDER BY path
  SQL
end

Upvotes: 1

Related Questions