From: Kirk Haines <khaines@...>
Date: 2004-04-16T05:37:34+09:00
Subject: Re: dbi: parent / child relationship

On Fri, 16 Apr 2004, Paul Vudmaska wrote:

> I've got a few situations where i'm selecting a parent child 
> relationship with a join. I'm looking for an elegant way of iterating 
> through the children. Using the join, the parent table data is redundant 
> when there are muliple children.
> 
> for example, some pseudo code:
> db.select_all('select * from parent,child where parent.id = 
> child.idParent') .each do |r|
>     if this_parent_id = old_parent_id
>        #must be a child, or first parent
>        paint_child
>    else
>         paint_new_parent
>     end
> end
> #or this.
> db.select_all('select id from parent').each do |r|
>     paint_parent
>     db.select_all('select * from child where idParent = ?,r['id']).each 
> do |r|
>        paint_parent
>     end
> end
> 
> Neither of these is elegant or efficient. Does anyone have a more ruby 
> way of  working with a db parent/child relationship?

Using Kansas, back wherever I was setting up my database handle, I'd 
define the relationship:

KSDatabase::Parent.to_many(:children, :Child, :idParent)

This says to create a relationship, 'children', from Parent to Child, 
relating the primary key of Parent to the idParent field of child.

Then, to do the query and iterate:

# ksdbh is the Kansas object.
ksdbh.select(:Parent).each do |parent|
  paint_parent
  parent.children do |child|
    paint_child
  end
end



Kirk Haines