Rails acts as nested set return descendant of all records in a has_many relation

Question

I am using the awesome_nested_set gem and am trying to get all the descendants of a has_many relationship. A User has many tasks shared to them and each task has many children. Ho do I get a list of a Users tasks_shared_to and all their children (note the children are not shared to the user individually)?

User model:

has_many :tasks_shared_to, through: :user_task_shares, source: :task

Task Model:

acts_as_nested_set
belongs_to :created_by, :class_name => 'User', :foreign_key => 'created_by_id'
belongs_to :parent, :class_name => 'Task', :foreign_key => 'parent_id'

I know this would be easy to do with a loop through each tasks_shared_to and then calling self_and_descendants but I would like to be able to do this without doing a separate SQL call within each loop.

Answer 1

I ended up ditching the awesome_nested_set gem and followed through this excellent blog:

http://hashrocket.com/blog/posts/recursive-sql-in-activerecord

I then modified the class methods to accept an array of objects, extracted to a module and added a few methods like so:

module RecursiveTree
  def self.included(recipient)
    recipient.extend(ModelClassMethods)
  end

  module ModelClassMethods
    def tree_for_ids(methods)
      self.where("#{table_name}.id IN (#{self.tree_sql_for_ids(self.sql_clean(methods))})")
    end

    def tree_for_id(id)
      self.where("#{table_name}.id IN (#{self.tree_sql_for_ids("id = #{id}")})")
    end

    def sql_clean(methods)
      sql_to_exec = ""
      methods.each do |method|
        sql_string = method.to_sql #note we can pass methods and they will not get executed unless we chain something to them - because of lazy instatiation
        #remove everything after the "ORDER" string as can't order more than once and order doesnt matter anyway
        sql_string = sql_string.slice(0..(sql_string.index('ORDER')-2)) unless sql_string.index('ORDER').nil?
        #add an OR to the front if there is already some text in the sql_to_exec
        sql_to_exec += "#{" OR " unless sql_to_exec.empty?}#{table_name}.id IN (#{sql_string})"
      end
      sql_to_exec.gsub! '*', 'id' #sub to only return the ids
    end

    def tree_sql_for_ids(sql_id_array)
      tree_sql = sql_id_array ? <<-SQL
        WITH RECURSIVE search_tree(id, path) AS (
            SELECT id, ARRAY[id]
            FROM #{table_name}
            WHERE #{sql_id_array}
          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
      : "NULL"
    end

    def ancestors_sql_for(instance)
      ancestors_sql = instance.id ? <<-SQL
        WITH RECURSIVE search_tree(id, path) AS (
            SELECT parent_id, ARRAY[parent_id]
            FROM #{table_name}
            WHERE id = #{instance.id}
          UNION ALL
            SELECT #{table_name}.parent_id, path || #{table_name}.parent_id
            FROM search_tree
            JOIN #{table_name} ON #{table_name}.id = search_tree.id
            WHERE NOT (#{table_name}.parent_id IS NULL)
        )
        SELECT id FROM search_tree ORDER BY path
      SQL
      : "NULL"
    end
  end

  #instance methods go here:
  def ancestors
    self.class.where("#{self.class.table_name}.id IN (#{self.class.ancestors_sql_for(self)})").order("#{self.class.table_name}.id")
  end

  def ancestor_complete
    ancestors.where(complete: true).any?
  end

  def top_level_parent
    is_top_level_parent? ? self : self.ancestors.where(parent_id: nil).first
  end

  def is_top_level_parent?
    self.parent.nil?
  end

  def descendants
    self.id ? self_and_descendants.where.not(id: self.id) : self.class.none #self.class.none return an empty ActiveRecord relation
  end

  def self_and_descendants
    self.id ? self.class.tree_for_id(self.id) : self.class.none #self.class.none return an empty ActiveRecord relation
  end
end

and then included in my task model:

class Task < ActiveRecord::Base
  include RecursiveTree #in the lib folder
...
end

Ten I can call with any number of methods eg:

u1 = User.find(1)
Task.tree_for_ids([u1.tasks_shared_to, u1.tasks_shared_to_through_groups])

This gets the job done in only 1 query!