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
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.

6 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.

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

    ReplyDelete
  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.

    ReplyDelete
  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?

    ReplyDelete
  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 (http://groups.google.com/group/sequel-talk?pli=1). Jeremy Evans and quite a few other smart Sequel people hang out there and might have some thoughts on how to do this.

    ReplyDelete
  6. ppax, Check out my new post http://steamcode.blogspot.com/2010/07/sequel-trees-redux.html and see if that helps.

    ReplyDelete