Thursday, July 1, 2010

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

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

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

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

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

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

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

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

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

Let me know if you have any questions or comments.


  1. Just tickles me to the bone to see folks picking the tree stuff up (the the generic tree plugin comes from my sequel_plus gem). Sequel's architecture really shines here because if you have a version of Sequel prior to 3.13.0, you can still get the tree plugin by installing sequel_plus gem. And if you have both installed and upgrade to > 3.13.0, the version included in Sequel is picked up automatically! Some very smart coding on Jeremy's part.

  2. how 'bout making the list in tree order.... i just cant figure it (noob here :P)

  3. ppax, If you saw my earlier comment with pre/post/in order discussion ... never mind. It would only apply for a binary tree which is not what we have here. So what do you mean "tree order". Let me know and I'll see if I can help you out.

  4. if a want to do for example, make a organization list.. i ended up listing the whole parent first, and then all the children below like this:
    -parent 1
    -parent 2
    -child 1 of parent 1
    -child 1 of parent 2

    and what i want
    -parent 1
    --child 1 of parent 1
    -parent 2
    --child 1 of parent 2

    how to do that in sequel?

  5. ppax, OK, I see what you want now. I'm not sure you can do this in a single call with Sequel (in fact I'm pretty sure you can't). I think you'd have to write a recursive method that would work its way through the tree calling itself for each child that it found. It I can get a few hours, I'll take a look at demonstrating how to do it. You might also want to ask on the Sequel mail list ( Jeremy Evans and quite a few other smart Sequel people hang out there and might have some thoughts on how to do this.

  6. ppax, Check out my new post and see if that helps.