duangan1945
2016-11-28 11:00
浏览 93
已采纳

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

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 |
+----+----------+------------+-----+
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doucang8303 2016-11-30 12:39
    已采纳

    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>";
    
    }
    
    点赞 打赏 评论

相关推荐 更多相似问题