capturing the right model behavior, do I use serialize() or a model relationship?

Question

I have a model called sites. A site will have several adjacent sites (and also be an adjacent site for other sites).

I am trying to determine how best to capture the list of adjacencies.

I have considered creating a text field in the site model and using serialize to store/retrieve the array of all of the adjacent sites. The problem with this is that I'd be violating DRY since there'd be no real relationship formed between the adjacent sites, and thus would have to store the list of adjacent sites for each site individually.

I started digging through some of the online docs for the has_and_belongs_to_many relationship, but in the examples I've found the relationship seems to always be between two different types of objects. Can I have a has_and_belongs_to_many relationship with the same object?

so:

class Site < ActiveRecord::Base  
    has_and_belongs_to :sites
end

or do I need to create a seperate table for adjacent sites?

Answer 1

It should be possible to do this, however it will involve playing about with the options on

has_and_belongs_to_many

You'll probably have a join table defined with a migration something along the lines of:

create_table :sites_associated_sites, :id => false do |t|
  t.integer :site_id
  t.integer :associated_site_id
end

Then in the model you'll need to setup has_and_belongs_to_many twice

class Site < ActiveRecord::Base  
    has_and_belongs_to_many  :sites, :join_table => 'sites_associated_sites'
    has_and_belongs_to_many  :associated_sites, :class_name => 'Site',
                             :join_table => 'sites_associated_sites'
end

This answer should be taken with a pinch of salt as this is more loud thinking than anything else, but definitely worth having a read up on the has_and_belongs_to_many method in the API: http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#M001836

Answer 2

I dug some more and found a good resource for this. The example they used is if you have a class of users, and users can have friends who are also users, but you don't want to create a separate table of friends.

What the author of the article did was create a join table with just the foreign keys, that allows the habtm relationship to exist without a third table.

Here's the link:

http://www.urbanpuddle.com/articles/2007/06/14/rails-mini-tutorial-user-habtm-friends

Answer 3

Notice that the solution you found works in one direction only:

>> Site.last.friends
[]
>> Site.last.friends << Site.first
[#<Site id: 1, name: "First", description: "The First", created_at: "2009-09-08 21:15:09", updated_at: "2009-09-08 21:15:09">]
>> Site.last.friends
[#<Site id: 1, name: "First", description: "The First", created_at: "2009-09-08 21:15:09", updated_at: "2009-09-08 21:15:09">]
>> Site.first.friends
[]

If you want it to work two ways, you can use something like:

class Site < ActiveRecord::Base
  has_and_belongs_to_many :l_adjacent_sites, :class_name => 'Site', :join_table => 'sites_sites', :foreign_key => 'l_site_id', :association_foreign_key => 'r_site_id'
  has_and_belongs_to_many :r_adjacent_sites, :class_name => 'Site', :join_table => 'sites_sites', :foreign_key => 'r_site_id', :association_foreign_key => 'l_site_id'
end

But the arcs are directed:

>> Site.first.r_adjacent_sites
[]
>> Site.last.r_adjacent_sites < Site.first
[#<Site id: 1, name: "First", description: "The First", created_at: "2009-09-08 21:15:09", updated_at: "2009-09-08 21:15:09">]
    >> Site.last.r_adjacent_sites
[#<Site id: 1, name: "First", description: "The First", created_at: "2009-09-08 21:15:09", updated_at: "2009-09-08 21:15:09">]
>> Site.first.l_adjacent_sites
[#<Site id: 4, name: "Fourth", description: "The fourth", created_at: "2009-09-08 21:48:04", updated_at: "2009-09-08 21:48:04">]

If what you want to represent is directed arcs, you'll be fine; I haven't figured yet a solution for nondirected arcs (apart from mysite.l_adjacent_sites + mysyte.r_adjacent_sites]).

EDIT

I tried to hack something to obtain a adjacent_sites named_scope or the like, but couldn't find anything; also, I'm not sure that a general solution (allowing you to filter results adding more conditions) actually exists.

Since doing l_adjacent_sites + r_adjacent_sites forces the (two) queries execution, I can only suggest something like:

def adjacent_sites options={}
  l_adjacent_sites.all(options) + r_adjacent_sites.all(options)
end

This should allow you to do things like:

@mysite.adjacent_sites :conditions => ["name LIKE ?", "f%"]

There are still issues, though:

• Sorting will not work, that is, you'll get a halfsorted set, like [1, 3, 5, 2, 4, 6]. If you need to sort results; you'll have to do it in ruby.

• Limit will only half-work: :limit => 1 will give you up to 2 results, as two queries will be executed.

But I'm positive that for most purposes you'll be ok.

Answer 4

Some additional digging turned up a method of accomplishing Bidrectional Relationships in the reference book The Rails Way (page 227). Unfortunately there are two problems with the solution in the text. First it was incomplete, though a complete version was available on the book's bug tracker/errata page

class BillingCode < ActiveRecord::Base
  has_and_belongs_to_many :related,
  :join_table => 'related_billing_codes',
  :foreign_key => 'first_billing_code_id',
  :association_foreign_key = 'second_billing_code_id',
  :class_name => 'BillingCode',
  :insert_sql => 'INSERT INTO related_billing_codes (`first_billing_code_id`, `second_billing_code_id`) VALUES (#{id}, #{record.id}), (#{record.id}, #{id})',
  :delete_sql => 'DELETE FROM related_billing_codes WHERE (`first_billing_code_id` = #{id} AND `second_billing_code_id` = #{record.id}) OR (`first_billing_code_id` = #{record.id} AND `second_billing_code_id` = #{id})'
end

The second problem is that the solution (which relies on an SQL statement inserting two records into the table within the same INSERT statement) is not supported by the default development database SQLite3.