使用Codeigniter闭包表显示分层数据

Question

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 1

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>";

}