notes on Mel's Peer Kit relationships...

gistfile1.rb

# Mel,  This is fugly, but it has the benefit of only using
# activerecord relations - here's why:
# 
# Using the has_many :through technique, we need to account for the
# possibility of the 'peer' being in either of the two 'belongs_to'
# relationships in the relation.  To keep ourselves mentally straight
# in this, the code refers to them as the 'left' and 'right' side.
# We need to do two queries and join them to get all the possible peers.
# There is a potentially better, but more complex solution to I'll
# outline below:


# create_kits.rb
class CreateKits < ActiveRecord::Migration
  def change
    create_table :kits do |t|
      t.string :name

      t.timestamps
    end
  end
end


# create_kit_relations.rb
class CreateKitRelations < ActiveRecord::Migration
  def change
    create_table :kit_relations do |t|
      t.integer :left_kit_id
      t.integer :right_kit_id

      t.timestamps
    end
  end
end


# kit.rb
class Kit < ActiveRecord::Base
  has_many :left_kit_relations,
           :class_name => "KitRelation",
           :foreign_key => :right_kit_id
           
  has_many :right_kit_relations,
           :class_name => "KitRelation",
           :foreign_key => :left_kit_id
  
  has_many :left_kits, :through => :left_kit_relations
  has_many :right_kits, :through => :right_kit_relations
  
  def peer_kits
    (left_kits + right_kits).uniq
  end
  
end


# kit_relation.rb
class KitRelation < ActiveRecord::Base
  belongs_to :left_kit, :class_name => "Kit"
  belongs_to :right_kit, :class_name => "Kit"
end


#exercise from the console:

k1 = Kit.create(:name => "First Kit")
k2 = Kit.create(:name => "Second Kit")
k3 = Kit.create(:name => "Third Kit")


# and you can create a relation with k1 on the left:
k1.right_kits << k2

# or k1 on the right:
k1.left_kits << k3

# and you can now see the peer relationships between all three:
k1.peer_kits
k2.peer_kits
k3.peer_kits


# a better solution to this would have a query that always assumed the
# first object is on the 'left' side, but for that we need to override
# the creation of the relation to create two entries in the
# kit_relations table, so it create like this:

k1.peer_kits << k2

# insert into kit_relations k1.id, k2.id
# insert into kit_relations k2.id, k1.id

# I might try to work up that example too...

Oh I forgot to add -

Make sure you have indexes on all the ID fields.

This if also ugly because it takes two queries to build your peer list.
the peer list can't be lazily traversed - if your lists get huge, that list join and uniq can become expensive.

The custom sql version I'm talking about implementing I think can be found in Joe Celko's "Sql for Smarties" book.

@bokmann: Thanks for this example. I'm going to have to let this one 'stew' a bit before I apply it.

Check this jist for a slight improvement:

https://gist.github.com/1912350

and one more slight improvement, depending on your opinion. This uses a custom sql migration to create a view that does the nasty join I was doing in ruby code above. Here is the migration:

class CreatePeerKitsView < ActiveRecord::Migration
def up
sql = "create view peer_kits as select left_kit_id, right_kit_id from kit_relations union select right_kit_id, left_kit_id from kit_relations"
execute(sql)
end

def down
sql = "drop view peer_kits"
execute(sql)
end
end

and here is the new code. replace the peer_kits method in Kit above with this:

def peer_kits
Kit.find_by_sql "select * from kits where id in (select right_kit_id from peer_kits where left_kit_id = #{id})"
end