Recursive self-referential many-to-many relationship

rubyonrails-talk
1

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.

2

Toby Rodwell 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,

3

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

4

Toby Rodwell 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,

5

Marnen Laibow-Koser wrote:

Toby Rodwell 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'.

6

Toby Rodwell wrote:

Marnen Laibow-Koser wrote:

Toby Rodwell 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,

7

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

8

Colin Law wrote:

Toby Rodwell 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,