frobak frobak - 8 days ago 3
MySQL Question

Displaying heirarchical data with Codeigniter closure table

Using Codeigniter, I have a need to display a list of Organisations. Some organisations will have sub-organisations, and possibly sub-organisaitons of sub-organisations, and as such will need to be displayed in the list underneath its parent, indented if you will.

I am using a closure table to store the organisational hierarchy, which works great for inserts, selecting children etc, but I've hit a wall when it comes to selecting all Organisations and their children in a single list/query.

The organisations table:

CREATE TABLE IF NOT EXISTS `organisations` (
`org_id` INT NOT NULL AUTO_INCREMENT,
`org_name` VARCHAR(60) NOT NULL,
`address1` VARCHAR(40) NULL DEFAULT NULL,
`address2` VARCHAR(40) NULL DEFAULT NULL,
`address3` VARCHAR(40) NULL DEFAULT NULL,
`town` VARCHAR(20) NULL DEFAULT NULL,
`county` VARCHAR(20) NULL DEFAULT NULL,
`pcode` VARCHAR(10) NULL DEFAULT NULL,
`phone` VARCHAR(12) NULL DEFAULT NULL,
`support_email` VARCHAR(60) NOT NULL,
`active` TINYINT(4) NULL DEFAULT '1',
PRIMARY KEY (`organisation_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


And the org_hierarchy table

CREATE TABLE IF NOT EXISTS `org_hierarchy` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`ancestor` INT(11) NOT NULL,
`descendant` INT(11) NOT NULL,
`lvl` INT(11) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


This is the method I'm using in my model to query the db and get children of an organisation:

public function get_children($org_id, $node_id = 0, $self = TRUE, $level = TRUE){

$this->db->select('t.org_id,t.org_name,t.org_label');
$this->db->from($this->closure_table." c1");
$this->db->join($this->table.' t','t.area_id = c1.descendant');
$this->db->join($this->closure_table.' c2', 'c2.lvl IN(1) AND c2.descendant = c1.descendant','LEFT');
$this->db->where('c1.ancestor',$node_id);
$this->db->where('t.org_id',$org_id);

if(!$self){$this->db->where('c1.descendant <>', $node_id);}

if($level){$this->db->where('c1.lvl = ', $level);}

$query = $this->db->get();

if(!$query->num_rows()){return FALSE;}

$result = $query->result();

return $result;
}


But how can I amend this query to display a complete list of all organisations grouped by parent organisation?

I can query and get a single organisation and its children with the below query, but how can I amend the query to get all organisations together with its children? Surely I have to be close?

SELECT o.* FROM organisations o
JOIN org_hierarchy h
ON (o.org_id = h.descendant)
WHERE h.ancestor = 3


I've tried many ways and I cant seem to change it to include all orgs?

Dump of org_hierarchy table

mysql> SELECT * FROM org_hierarchy
-> ;
+----+----------+------------+-----+
| id | ancestor | descendant | lvl |
+----+----------+------------+-----+
| 1 | 2 | 2 | 0 |
| 2 | 3 | 3 | 0 |
| 3 | 4 | 4 | 0 |
| 4 | 3 | 5 | 1 |
| 5 | 5 | 5 | 0 |
| 7 | 3 | 6 | 2 |
| 8 | 5 | 6 | 1 |
| 9 | 6 | 6 | 0 |
+----+----------+------------+-----+

Answer

Alright below you'll find an example how to achieve what you want

Your Model should look like :

class Organisations_Model extends CI_Model
{

    private $arrOrganisationsGroupedByParent = array();
    private $objOrganisationsTree = false;

    public function loadOrganisations()
    {
        $query = $this->db
            ->select('o.*, if (oh.ancestor!=o.org_id, oh.ancestor, 0) AS parent', false)
            ->from("organisations o")
            ->join("org_hierarchy AS oh", "o.org_id = oh.descendant","left")
            ->get();

        $arrOrganisations = $query->result("Organisations_Object");

        foreach($arrOrganisations AS $objItem)
        {
            $this->arrOrganisationsGroupedByParent[$objItem->parent][] = $objItem;
        }
    }

    public function getTree()
    {
        $this->loadOrganisations();
        $this->objOrganisationsTree = new Organisations_Object();
        $this->createTree($this->objOrganisationsTree);
        return $this->objOrganisationsTree;
    }


    private function createTree($node)
    {
        if (isset($this->arrOrganisationsGroupedByParent[$node->org_id]))
        {
            foreach($this->arrOrganisationsGroupedByParent[$node->org_id] AS $objItem)
            {
                //echo $objItem->org_id."<br />";
                $node->addChild($objItem);
            }
        }

        foreach($node->arrChildObjects AS $objChild)
        {
            $this->createTree($objChild);
        }
    }
}

class Organisations_Object
{
    public $arrChildObjects = array();
    public $org_id = 0;
    public $parent = -1;

    public function addChild($node)
    {
        $this->arrChildObjects[] = $node;
    }
}

Note: You have in your model an additional Class called Organisations_Object - don't miss that one !

After that, just simply call the getTree Method in your Controller.

As an Example for your Controller just put these 2 functions in and call it from your browser

public function testtree()
{
    $this->load->model("Organisations_Model");
    $objTree = $this->Organisations_Model->getTree();
    $this->printTree($objTree);
}

private function printTree($objTree)
{
    echo "<ul>";

    foreach($objTree->arrChildObjects AS $objItem)
    {
        echo "<li>".$objItem->org_id."#".$objItem->org_name;
        if (count($objItem->arrChildObjects) > 0)
        {
            $this->printTree($objItem);
        }
        echo "</li>";
    }
    echo "</ul>";

}