# Recursive self-referential many-to-many relationship **URL:** \ **Category:** Rails\ **Created:** [June 17, 2010, 12:07am UTC](https://www.ruby-forum.com/t/recursive-self-referential-many-to-many-relationship/190142 "2010-06-17T00:07:30Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![tobyr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ebca7d/32.png) [@tobyr](https://www.ruby-forum.com/u/tobyr)\ **Post date:** [June 17, 2010, 12:07am UTC](https://www.ruby-forum.com/t/recursive-self-referential-many-to-many-relationship/190142/1 "2010-06-17T00:07:30Z")
I have a ‘Circuit’ table, and a ‘Glue’ table to perform a many-to-many join on the circuit records, such at a circuit can have many subcircuits (its component parts) and many supercircuits (circuits which it itself is a part of). has\_and\_belongs\_to\_many :subcircuits, :class\_name =\> “Circuit”, :join\_table =\> “circuit\_glue”, :association\_foreign\_key =\> “PTR\_component”, :foreign\_key =\> “PTR\_circuit” has\_and\_belongs\_to\_many :supercircuits, :class\_name =\> “Circuit”, :join\_table =\> “circuit\_glue”, :foreign\_key =\> “PTR\_component”, :association\_foreign\_key =\> “PTR\_circuit” With this, I can do successfully things like my\_circuit.subcircuits[0].name but if I try my\_circuit.subcircuits[0].subcircuits.any\_method … I get a NoMethodError: You have a nil object when you didn’t expect it! The objects in the cct.subciruits array do not appear to ‘full’ circuit objects in as much as they do not repsonsd to ‘subcircuits’ To build a recursive list of subcircuits I find I have to resort to self.subcircuits.each { |sc| subcirc\_array.push(Circuit.find(:all).detect { |c| c.absid.to\_s == sc.PTR\_component })} … which seems pretty slow and laborious. Any advice on how to speed this up, and/or get …subciruits[n].subcircuits to work as required? Thanks in advance. ---
**Author:** ![tobyr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ebca7d/32.png) [@tobyr](https://www.ruby-forum.com/u/tobyr)\ **Post date:** [June 17, 2010, 12:10am UTC](https://www.ruby-forum.com/t/recursive-self-referential-many-to-many-relationship/190142/2 "2010-06-17T00:10:07Z")
Toby R. wrote: > I have a ‘Circuit’ table, and a ‘Glue’ table to perform a many-to-many > join on the circuit records, such at a circuit can have many subcircuits > (its component parts) and many supercircuits (circuits which it itself > is a part of). In other words, you have a hierarchical tree of circuits. > has\_and\_belongs\_to\_many :subcircuits, > :class\_name =\> “Circuit”, > :join\_table =\> “circuit\_glue”, > :association\_foreign\_key =\> “PTR\_component”, > :foreign\_key =\> “PTR\_circuit” > has\_and\_belongs\_to\_many :supercircuits, > :class\_name =\> “Circuit”, > :join\_table =\> “circuit\_glue”, > :foreign\_key =\> “PTR\_component”, > :association\_foreign\_key =\> “PTR\_circuit” > > With this, I can do successfully things like > my\_circuit.subcircuits[0].name > but if I try > my\_circuit.subcircuits[0].subcircuits.any\_method > … I get a NoMethodError: You have a nil object when you didn’t expect > it! > The objects in the cct.subciruits array do not appear to ‘full’ circuit > objects in as much as they do not repsonsd to ‘subcircuits’ > To build a recursive list of subcircuits I find I have to resort to > > self.subcircuits.each { |sc| > subcirc\_array.push(Circuit.find(:all).detect { |c| c.absid.to\_s == > sc.PTR\_component })} > … which seems pretty slow and laborious. Any advice on how to speed > this up, and/or get …subciruits[n].subcircuits to work as required? > Thanks in advance. You want awesome\_nested\_set, which will let you do that with one query. The Glue model is unnecessary. ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![tobyr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ebca7d/32.png) [@tobyr](https://www.ruby-forum.com/u/tobyr)\ **Post date:** [June 17, 2010, 9:12pm UTC](https://www.ruby-forum.com/t/recursive-self-referential-many-to-many-relationship/190142/3 "2010-06-17T21:12:17Z")
Marnen Laibow-Koser wrote: > You want awesome\_nested\_set, which will let you do that with one query. > The Glue model is unnecessary. Thanks very much for the info. I’ve had a quick look at the documentation and I see it makes use of fields :lft and :rgt -I guess I can use aliases for these, along the lines of :lft =\> “my\_field”, as I don’t have control of the database? Also, do you know if awesome\_nested\_set will be faster than what I’m doing currently, or just more convenient? regards ---
**Author:** ![tobyr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ebca7d/32.png) [@tobyr](https://www.ruby-forum.com/u/tobyr)\ **Post date:** [June 18, 2010, 12:05am UTC](https://www.ruby-forum.com/t/recursive-self-referential-many-to-many-relationship/190142/4 "2010-06-18T00:05:23Z")
Marnen Laibow-Koser wrote: > Toby R. wrote: > > > Marnen Laibow-Koser wrote: > You may be able to use aliases, but how does this solve the basic > problem of adding fields to the DB? > I don’t add fields (or even records) to this database - I just use RoR > as way a way to get data out in a useful format. Anyway, thanks for the > pointer to ‘awesome’. ---
**Author:** ![tobyr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ebca7d/32.png) [@tobyr](https://www.ruby-forum.com/u/tobyr)\ **Post date:** [June 17, 2010, 11:22pm UTC](https://www.ruby-forum.com/t/recursive-self-referential-many-to-many-relationship/190142/5 "2010-06-17T23:22:49Z")
Toby R. wrote: > Marnen Laibow-Koser wrote: > > > You want awesome\_nested\_set, which will let you do that with one query. > > The Glue model is unnecessary. > > Thanks very much for the info. I’ve had a quick look at the > documentation and I see it makes use of fields :lft and :rgt -I guess I > can use aliases for these, along the lines of :lft =\> “my\_field”, as I > don’t have control of the database? If you don’t have control of the database, you should not be doing the project. Really. You may be able to use aliases, but how does this solve the basic problem of adding fields to the DB? > Also, do you know if > awesome\_nested\_set will be faster than what I’m doing currently, or just > more convenient? Probably both. As I said, the advantage of nested sets is that you can fetch all descendants to arbitrary depth with one query. > regards ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![tobyr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ebca7d/32.png) [@tobyr](https://www.ruby-forum.com/u/tobyr)\ **Post date:** [June 18, 2010, 9:38am UTC](https://www.ruby-forum.com/t/recursive-self-referential-many-to-many-relationship/190142/6 "2010-06-18T09:38:44Z")
On 17 June 2010 22:22, Marnen Laibow-Koser [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Toby R. wrote: > … > If you don’t have control of the database, you should not be doing the > project. Really. I don’t think this is a valid general point. There are often situations involving a legacy database where it is not feasible to change the database but where RoR makes sense for additional functionality, particularly web access. Colin ---
**Author:** ![tobyr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ebca7d/32.png) [@tobyr](https://www.ruby-forum.com/u/tobyr)\ **Post date:** [June 18, 2010, 12:21am UTC](https://www.ruby-forum.com/t/recursive-self-referential-many-to-many-relationship/190142/7 "2010-06-18T00:21:49Z")
Toby R. wrote: > Marnen Laibow-Koser wrote: > > > Toby R. wrote: > > > > > Marnen Laibow-Koser wrote: > > > You may be able to use aliases, but how does this solve the basic > > problem of adding fields to the DB? > > I don’t add fields (or even records) to this database - I just use RoR > > as way a way to get data out in a useful format. Anyway, thanks for the > > pointer to ‘awesome’. You’ll have to add the lft and rgt fields to the database to use nested sets. That’s the way the data structure works. ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![tobyr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ebca7d/32.png) [@tobyr](https://www.ruby-forum.com/u/tobyr)\ **Post date:** [June 18, 2010, 2:04pm UTC](https://www.ruby-forum.com/t/recursive-self-referential-many-to-many-relationship/190142/8 "2010-06-18T14:04:15Z")
Colin L. wrote: > On 17 June 2010 22:22, Marnen Laibow-Koser [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > Toby R. wrote: > > … > > If you don’t have control of the database, you should not be doing the > > project. �Really. > > I don’t think this is a valid general point. There are often > situations involving a legacy database where it is not feasible to > change the database but where RoR makes sense for additional > functionality, particularly web access. Rails can work with legacy DBs to a certain extent. It wants its DBs set up a certain way, though, and that’s where the developer needing to have control of the DB comes in. It’s particularly necessary if the OP wants to make the transition to nested sets, since you can’t do that without storing the left and right values. > Colin ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid