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...

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