-
-
Save bokmann/1912278 to your computer and use it in GitHub Desktop.
# 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... |
@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:
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
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.