Sequel Trees

Edit: Jeremy Evans had a couple of suggestions which I've made in the code below. Use a real foreign key for the parent_id,use a portable text column, and finally don't give a parent_id for the root (Alice) of the tree.

In Sequel 3.13.0, Jeremy Evans has added a few new plugins. Most interesting to me was the one for trees, so I'm going to outline how to use it here.

Here's a program that sets up and uses an employee/manager tree structure. We do our normal set up of the database, create the model (including adding the :tree plugin), and create a few employees. Finally, we run through a few of the common tree functions. The code is pretty well commented, so I'll present it and you can ask questions if you have any.

require 'rubygems'
require 'sequel'

# Create an in-memory database
DB = Sequel.sqlite 

# Create the employees table with a name and parent_id.
DB.create_table :employees do 
    primary_key :id
    foreign_key :parent_id, :employees
    String :name, :text=>true, :unique=>true
end

# Create the Employee model. 
class Employee < Sequel::Model
    plugin :tree # Uses :parent_id field for parent
end

# Create the top level employee
alice = Employee.create(:name => "Alice")

# Create Alice's direct reports
bob = Employee.create(:name => "Bob", :parent_id => alice.id)
charlene = Employee.create(:name => "Charlene", :parent_id => alice.id)
dave = Employee.create(:name => "Dave", :parent_id => alice.id)

# Create Charlene's direct reports
ellen = Employee.create(:name => "Ellen", :parent_id => charlene.id)
frank = Employee.create(:name => "Frank", :parent_id => charlene.id)

# Find the top level employee for Ellen
president = ellen.root
puts "Ellen's president: #{president.name}"

# Find Ellen's management chain.
managers = ellen.ancestors
managers.each do | a |
    puts "Ellen's manager: #{a.name}"
end

# Find Charlene's coworkers.
coworkers = charlene.siblings
coworkers.each do | s |
    puts "Charlene's coworker: #{s.name}"
end

# Find Charlene's coworkers and Charlene.
self_coworkers = charlene.self_and_siblings
self_coworkers.each do | s |
    puts "Charlene's coworker (or Charlene): #{s.name}"
end

Let me know if you have any questions or comments.