Recursively load all children/parent records in hierarchy

-- Rails: 2.3.8, Ruby: 1.9.2v180, MySQL: 5.1.56 --

I have a recursive model called ExternalCategory which

  belongs_to :parent, :class_nam开发者_StackOverflowe => "ExternalCategory", :foreign_key => "parent_id"

and

  has_many :children, :class_name => "ExternalCategory", :foreign_key => "parent_id"

I display this hierarchy in a paginated view where only some number of top-level categories are initially visible. All ancestors of these top-level categories get loaded to the page but are only displayed via js.

An ExternalCategory may have over 1000 ancestors, so without eager loading these pages can take a huge amount of time to load. That being said, I've been working on adding eager loading in an appropriate fashion but I'm not quite sure what that would be. Perhaps you can help.

At the moment, I have a scope for the top_level_categories inside of ExternalCategory:

named_scope :top_level_categories, :conditions => "parent_id is null"

and then tacked on to the end of the has_many :children ... line up above I've added this caveat:

   , :include => :children

So now whenever I load the children of any ExternalCategory, the children of those children get loaded, along with the children of all those children, and so on. Ideally I wouldn't be doing this in the model itself but rather in the scope, but right now I'm okay with that bit of crappiness.

The main problem with all this is that I'm building up row ids in my table which reference the current element's id as well as all of it's ancestors' ids. This id gets used by the js when expanding/collapsing any category's children view. In order to create these ids, I access the current category's ancestors with recursive calls to #parent. What I end up with in my log is a ton of identical lines that look like this:

CACHE (0.0ms) SELECT * FROM external_categories WHERE (external_categories.id = 216)

If I avoid giving the rows all unique ids, it completely breaks the page (obviously), but things start loading lickity split.

Any advice, alternate perspectives or what have you would be immensely appreciated at this point.

Thanks in advance!

---

The built in acts_as_tree plugin is not very good at this, but there are alternatives like ancestry which support root_id to address this specific problem.

The approach that doesn't require changing anything involves fetching one "level" of depth after the next until you run out of results, trimming your retrieval time dramatically.

The better approach is to add a root_id column that will make fetching all of the records in a particular branch very fast. You will have to compute this value in your migration, though, to apply it correctly. That can be a slow operation if it is only done once, though.