# How do you create a tree strucutre with ActiveRecord **URL:** \ **Category:** Rails\ **Created:** [January 12, 2006, 7:15am UTC](https://www.ruby-forum.com/t/how-do-you-create-a-tree-strucutre-with-activerecord/52254 "2006-01-12T07:15:52Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![paulbutcher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/a5b964/32.png) [@paulbutcher](https://www.ruby-forum.com/u/paulbutcher)\ **Post date:** [January 12, 2006, 7:15am UTC](https://www.ruby-forum.com/t/how-do-you-create-a-tree-strucutre-with-activerecord/52254/1 "2006-01-12T07:15:52Z")
I want to build an application that has the concept of administrative domains. What I mean by this is that administrators have access to different data, based on what domains they are a member of. The domain strucutre is hierarchical. Here is an example: - MLB - AL - East - Yankees - Red Sox … - Central - West - NL - East - Central - West Now image I could put users at any level of the domain, possibly even at multple levels. If the user has administrative privleges, the user would be able to edit the user accounts for other users in there domain, but not users in other domains. So, Derek Jeter would be a user in the Yankees domain, George Steinbrenner and admin user in the Yankees domain, David Ortiz a user in the Red Sox domain, and Bud Selig an admin user in the MLB domain. Since Ortiz and Jeter are just non-admin users, they can’t edit any other user accounts. Since steinbrenner is ad admin user in the Yankees domain, he can edit Jeter’s account, but not Ortiz’s, because Ortiz is not in Steinbrenner’s domain. Bud Selig could edit both Jeter and Ortiz’s account, because they are in sub-domains of his domain. The database structure would have to look something like this: Table: Domains ±—±----------±--------+ | id | parent\_id | name | ±—±----------±--------+ | 1 | NULL | MLB | | 2 | 1 | AL | | 3 | 2 | East | | 4 | 3 | Yankees | | 5 | 3 | Red Sox | ±—±----------±--------+ parent\_id is a FK to another domain id. A NULL parent\_id indicates it is a top-level domain. So there would be a users and a domain\_users table, and Domain model object would have a has\_many\_and\_belongs\_to\_many relationship with users. But the problem is that would only allow me to query what users are directly in a given domain. In order to determine what users are in a given domain, you have to include the child domains has well. So what users are in domain MLB? The query would be: SELECT \* from domain\_users where domain\_id IN (1,2,3,4,5) Then for steinbrenner it would be: SELECT \* from domain\_users where domain\_id IN (3) But how do you write a query to know what child, grandchild, etc domains to use? ---
**Author:** ![paulbutcher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/a5b964/32.png) [@paulbutcher](https://www.ruby-forum.com/u/paulbutcher)\ **Post date:** [January 12, 2006, 7:26am UTC](https://www.ruby-forum.com/t/how-do-you-create-a-tree-strucutre-with-activerecord/52254/2 "2006-01-12T07:26:32Z")
Hi the easiest way is …drum roll … acts\_as\_tree: [http://api.rubyonrails.com/classes/ActiveRecord/Acts/Tree/ClassMethods.html](http://api.rubyonrails.com/classes/ActiveRecord/Acts/Tree/ClassMethods.html) hope that helps. Mark On 1/11/06, Paul B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > ``` > ... > > ``` > > users in other domains. So, Derek Jeter would be a user in the Yankees > Table: Domains > parent\_id is a FK to another domain id. A NULL parent\_id indicates it is a > > Rails mailing list > removed\_email\_address@domain.invalid > [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails) – ---
**Author:** ![paulbutcher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/a5b964/32.png) [@paulbutcher](https://www.ruby-forum.com/u/paulbutcher)\ **Post date:** [January 12, 2006, 7:27am UTC](https://www.ruby-forum.com/t/how-do-you-create-a-tree-strucutre-with-activerecord/52254/3 "2006-01-12T07:27:39Z")
Sounds like you are looking for ‘acts\_as\_tree’ Basically you define a model table like this… id parent\_id other\_stuff models\_count * * * then in your model class Model \< AR::Base acts\_as\_tree :order=\>“other\_stuff”, :counter\_cache=\>true end then you can do all sorts of fun stuff like model.children model.parents model.ancestors model.siblings The counter cache will keep the ‘models\_count’ column up to date with the number of children, so you only need one query for that information. \_Kevin ---
**Author:** ![paulbutcher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/a5b964/32.png) [@paulbutcher](https://www.ruby-forum.com/u/paulbutcher)\ **Post date:** [January 12, 2006, 4:54pm UTC](https://www.ruby-forum.com/t/how-do-you-create-a-tree-strucutre-with-activerecord/52254/4 "2006-01-12T16:54:14Z")
acts\_as\_tree in ActiveRecord works very well for me read “Agile Web D. in Rails” for more information ---
**Author:** ![paulbutcher](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/a5b964/32.png) [@paulbutcher](https://www.ruby-forum.com/u/paulbutcher)\ **Post date:** [January 12, 2006, 3:59pm UTC](https://www.ruby-forum.com/t/how-do-you-create-a-tree-strucutre-with-activerecord/52254/5 "2006-01-12T15:59:26Z")
Actually, the biggest problem you have is that you listed the Yankees ahead of the Red Sox. 🙂 Ken – Kenneth A. Kousen, Ph.D. President Kousen IT, Inc. [http://www.kousenit.com](http://www.kousenit.com) [http://www.kousenit.com](http://www.kousenit.com) [mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) removed\_email\_address@domain.invalid * * * From: removed\_email\_address@domain.invalid [[mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)] On Behalf Of Paul B. Sent: Thursday, January 12, 2006 1:14 AM To: removed\_email\_address@domain.invalid Subject: [Rails] How do you create a tree strucutre with ActiveRecord I want to build an application that has the concept of administrative domains. What I mean by this is that administrators have access to different data, based on what domains they are a member of. The domain strucutre is hierarchical. Here is an example: - MLB - AL - East - Yankees - Red Sox … - Central - West - NL - East - Central - West Now image I could put users at any level of the domain, possibly even at multple levels. If the user has administrative privleges, the user would be able to edit the user accounts for other users in there domain, but not users in other domains. So, Derek Jeter would be a user in the Yankees domain, George Steinbrenner and admin user in the Yankees domain, David Ortiz a user in the Red Sox domain, and Bud Selig an admin user in the MLB domain. Since Ortiz and Jeter are just non-admin users, they can’t edit any other user accounts. Since steinbrenner is ad admin user in the Yankees domain, he can edit Jeter’s account, but not Ortiz’s, because Ortiz is not in Steinbrenner’s domain. Bud Selig could edit both Jeter and Ortiz’s account, because they are in sub-domains of his domain. The database structure would have to look something like this: Table: Domains ±—±----------±--------+ | id | parent\_id | name | ±—±----------±--------+ | 1 | NULL | MLB | | 2 | 1 | AL | | 3 | 2 | East | | 4 | 3 | Yankees | | 5 | 3 | Red Sox | ±—±----------±--------+ parent\_id is a FK to another domain id. A NULL parent\_id indicates it is a top-level domain. So there would be a users and a domain\_users table, and Domain model object would have a has\_many\_and\_belongs\_to\_many relationship with users. But the problem is that would only allow me to query what users are directly in a given domain. In order to determine what users are in a given domain, you have to include the child domains has well. So what users are in domain MLB? The query would be: SELECT \* from domain\_users where domain\_id IN (1,2,3,4,5) Then for steinbrenner it would be: SELECT \* from domain\_users where domain\_id IN (3) But how do you write a query to know what child, grandchild, etc domains to use?