Sequel Trees Redux

In a previous post, I discussed Sequel Trees. I had a question from that post on viewing the tree in "tree order". There are a couple of ways to do this one is recursive, which we'll use here. The other, from Jeremy Evans, is to use the rcte_tree plugin and a database that supports it. The latter is completely beyond me, so we'll go with the former.

Here, we're going to start out the same way we did last time by building up a database for a company. We've added a few things though. First, we have a title for each of the employees and second, we've added another top level employee (President) and a few additional employees under him. Once we have the structure in place, we get the roots (Presidents) of the model using Employee.roots and then for each of those employees, we loop calling the put_management_chain function. put_management_chain will print the title and name of the employee and then call itself recursively with each of the employees direct reports, children. One nice thing to note here is the level parameter to the function which is used in the puts to give us the proper indentation by taking three spaces and multiplying it by the indentation level.

Here's the code for the full program:

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
    String :title, :text=>true
end

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

# Puts out the management chain for an employee recursively. The level is used
# to print out some spaces in front of the name to show the relationship.
def put_management_chain(e, level)
    puts "#{"   "*level}#{e.title}: #{e.name}"
    direct_reports = e.children
    direct_reports.each do | d |
        put_management_chain(d, level+1)
    end
end

# Create a top level employee, Alice.
alice = Employee.create(:name => "Alice", :title => "President")

# Create Alice's direct reports.
bob = Employee.create(:name => "Bob", :title => "Vice President", :parent_id => alice.id)

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

# Create Charlene's and Dave's direct reports.
ellen = Employee.create(:name => "Ellen",  :title => "Software Engineer 1", :parent_id => charlene.id)
frank = Employee.create(:name => "Frank",  :title => "Software Engineer 2", :parent_id => charlene.id)
gerald = Employee.create(:name => "Gerald",  :title => "Software Engineer 2", :parent_id => dave.id)

# Create another top level employee, Alvin.
alvin = Employee.create(:name => "Alvin", :title => "President" )

# Create Alvin's direct reports.
bill = Employee.create(:name => "Bill",  :title => "Vice President", :parent_id => alvin.id)

# Create Bill's direct reports.
cedric = Employee.create(:name => "Cedric",  :title => "Manager", :parent_id => bill.id)
dale = Employee.create(:name => "Dale",  :title => "Manager", :parent_id => bill.id)

# Create Cedric's and Dale's direct reports.
edward = Employee.create(:name => "Edward",  :title => "Software Engineer 1", :parent_id => cedric.id)
felicia = Employee.create(:name => "Felicia",  :title => "Software Engineer 2", :parent_id => dale.id)

# Find all of the employees that don't have managers.
top_level = Employee.roots

# Print out the management structure for each of the top level managers.
top_level.each do | r |
    put_management_chain(r, 0)
    puts
end

As always, let me know if you have questions or comments.