Hierarchy of Objects with Sequel's many_to_many

posted 2012-Mar-20

I absolutely love Sequel, but the documentation for the plethora of options available for associate often confuse me.

If you have a table of objects that you want to arrange in a simple multiple parent/child hierarchy—multiple inheritance in ObjJob—whose data model looks like this:

Sequel.migration do
  change do
    create_table :items do
      primary_key :id
      # ...other properties...
    end
    create_table :hierarchy do
      primary_key :id
      foreign_key :parent_id, :items
      foreign_key :child_id,  :items
    end
  end
end

…then you can create child/parent many_to_many relationships like so:

class Item < Sequel::Model
  many_to_many :parents,  class: :Item, join_table: :hierarchy, left_key: :child_id,  right_key: :parent_id
  many_to_many :children, class: :Item, join_table: :hierarchy, left_key: :parent_id, right_key: :child_id
end

Seen in action:

>> p1 = Item.create name:"parent1"
#=> #<Item @values={:id=>1, :name=>"parent1"}>
>> p2 = Item.create name:"parent2"
#=> #<Item @values={:id=>2, :name=>"parent2"}>
>> c1 = Item.create name:"child1"
#=> #<Item @values={:id=>3, :name=>"child1"}>
>> c2 = Item.create name:"child2"
#=> #<Item @values={:id=>4, :name=>"child2"}>
>> DB[:hierarchy] << { parent_id:p1.pk, child_id:c1.pk }
#=> 1
>> DB[:hierarchy] << { parent_id:p1.pk, child_id:c2.pk }
#=> 2
>> DB[:hierarchy] << { parent_id:p2.pk, child_id:c1.pk }
#=> 3
>> p1.children
#=> [#<Item @values={:id=>3, :name=>"child1"}>, #<Item @values={:id=>4, :name=>"child2"}>]
>> p2.children
#=> [#<Item @values={:id=>3, :name=>"child1"}>]
>> c1.parents
#=> [#<Item @values={:id=>1, :name=>"parent1"}>, #<Item @values={:id=>2, :name=>"parent2"}>]
>> c2.parents
#=> [#<Item @values={:id=>1, :name=>"parent1"}>]
>> c2.children
#=> []