CODEWITHSUNDEEP
ruby-on-railsactiverecord
user4547292
user4547292

Reputation:

Rails self-referential hierarchical relationship

I've read Rails guides and tried a few different things w/Active Record but haven't been able to figure out what the best way to do this is.

I need to set up a self-referential (users to users) relationship that is hierarchical. It usually would be no more than 5 levels high, but should be able to scale up infinitely.

I've tried creating a UserHierarchy model with a DB schema like this:

parent | child | level

However, managing this is a bit too difficult and too complicated to handle.

What's the best way in Rails to do a self-referential hierarchical relationship? I've checked out gems like ancestry, but the majority of them use class inheritance and don't work well for self-referential relationships. It's a many-to-many, self-referential hierarchy (in MySQL).

Upvotes: 1

Views: 1441

Answers (1)

Holger Just
Holger Just

Reputation: 55833

Ancestry is one of the gems that is esp. well suited for object tree structures (what you call self-referential hierarchical relationships). You should have a more detailed look at it.

Generally, there are about four common ways to store trees in a SQL database:

  • Simple parent pointers. You just add a new column called parent_id to your model holding the ID of the parent object. This allows easy inserts and is well suited for single-level hierarchies but is generally difficult to use for deeper hierarchies and is thus generally not used as the primary mechanism (although it is sometimes combined with other mechanisms)
  • Nested Sets. You define your trees as a structure of nested sets. This is typically implemented with a right and a left column which are populated with numbers to define the set. It allows efficient querying but is a bit tricky when inserting values. Esp. when having concurrent changes to a tree, it is sometimes prone to inconsistencies. This model is e.g. used to the awesome_nested_set gem.
  • Materialized Paths. This is the model e.g. used by ancestry. It stores the full parent path of all elements. This allows for efficient inserting and querying. Changing a tree is bit more expensive.
  • Closure Trees. This mechanism stores for each element all of its parents in a table. This is e.g. used by the closure_tree gem.

Generally, all these options allow to store a tree of objects, i.e. a hierarchical structure of objects of the same class (an ActiveRecord model in this case).

Which one to use depends on which trade-offs are more important for your specific use-case. Most importantly, you should figure out if you are changing trees often (e.g. moving sub-trees around or adding only leaves) and how you are querying the tree (e.g. do you only need direct children, do you need whole sub-trees, do you need to filter) and chose the appropriate solution based on that.

Upvotes: 3

Related Questions