Creating a Tree from a self referencing DB table

Today, I had to create a tree structure for companies that had child companies. In the DB, it was stored as a self referencing table, with the top parent having a parent ID of null.
I wanted to display the tree using unordered lists <ul>. It wasn’t as hard as I thought!

I’m making this class a Zend_View_Helper, but if you aren’t using ZF, don’t be put off, the logic is still the same!

First up, I made a DB method in my DB class to get immediate children:

    /** 
     *  @return Zend_Db_Table_Rowset
     */
    public function getOrganisationsByParent($parent_id)
    {
        $select = $this->select()
                       ->where('parent_id = '.$parent_id)
                       ->order('name ASC');
        return $this->fetchAll($select);
    }

Next, I created my View Helper class. All it does is grab the children, append to the html, and for each child, the method runs itself!

<?php

class ESOS_View_Helper_CompanyTree extends Zend_View_Helper_Abstract
{
    /** @var string $html */
    private $html;
    
    /** @var ESOS_Repository_Organisation */
    private $org_db;
    
    
    public function companyTree($parent_id)
    {
        $this->tree = array();
        $this->org_db = ESOS_Service_Locator::getInstance()->getOrganisationSvc()->getRepository();
        $this->getChildren($parent_id);
        return $this->html;
    }
    
    public function getChildren($parent)
    {
        $orgs = $this->org_db->getOrganisationsByParent($parent);
        foreach($orgs as $org)
        { 
            $this->html .= '<li>'.$org->getName().'<ul>';
            $this->getChildren($org->getId()); 
            $this->html .= '</ul></li>';
        } 
    
    }
}

Easy as that! Which produced for me (with limited DB data):

ACME Products Inc.

  • Acme Accounting Solutions
  • Acme Reseach & Development
    • Acme Prototypes Limited

Or if you’d prefer to see the HTML:

<li>ACME Products Inc.
<ul class=”children”>
<li>Acme Accounting Solutions<ul></ul></li>
<li>Acme Reseach &amp; Development
<ul>
<li>Acme Prototypes Limited<ul></ul></li>
</ul>
</li>
</ul>
</li>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s