Full Tree Selects via Materialized Paths

At my internship I have been working on this really awesome in-house analytics systems.  One of the database tables used in this project behaves as a self-referential tree.  Since I am using Ruby on Rails for this project, I decided to go with the acts_as_tree plugin on this particular model.  This plugin adds various tree related methods with a minimal requirement that a foreign key column named “parent_id” exists in the corresponding table.

This plugin functioned as expected and something can certainly be said for sheer simplicity that this particular tree structure employs.  The complication arose when my application called for a full select of all the descendants from a given node.  This particular tree structure doesn’t support an efficient way to grab all of the corresponding descendant records.

Read the rest of this entry »