Bagaimana Bekerja dengan hierarchical data trees di CodeIgniter 2 ?

Update : Pustaka yang dibuat oleh thunder[1] dan update pustaka thunder yang mendukung multiple root [2] sudah tidak kompatibel lagi dengan CI2, jika ingin menggunakan Nested Set di CI2 sebaiknya menggunakan ORM Datamapper  [2]

===Kadaluarsa===
Untuk bekerja dengan hierarchical data trees di CodeIgniter 2 (tanpa menggunakan ORM pihak ketiga seperti Doctrine), maka dapat menggunakan library yang sudah dibuat oleh thunder [1].

Berikut adalah snippet kode untuk mengambil data 1 level dibawah parent pada sebuah struktur nested set (Kode lengkap dapat dilihat di : https://bitbucket.org/wildanm/e-office/src/a4d01d801a81/application/modules/kepegawaian/models/unit_organisasi_model.php) :

/**
         * @TODO - Buat agar query nya tidak depend ke unit_organisasi saja
         *       Dan dipindah ke nested_sets_model
         *       - Algoritma lebih dioptimasi
         * @see http://stackoverflow.com/questions/7582292/nested-set-model-query-node-childs-only-1-level-below-and-all-parents-above
         */
        function getTreeWithDepth($node,$depth)
        {
                $table      =       $this->table_name;
                $leftcol    =       $this->left_column_name;
                $rightcol   =       $this->right_column_name;
                $leftval    = (int) $node[$leftcol];
                $rightval   = (int) $node[$rightcol];

                $sql = "SELECT node.kode_unit, node.nama, (COUNT(parent.kode_unit) - (sub_tree.depth + 1)) AS depth
                                FROM $table AS node,
                                $table AS parent,
                                $table AS sub_parent,
                                (
                                    SELECT node.kode_unit, node.nama, (COUNT(parent.kode_unit) - 1) AS depth
                                    FROM $table AS node,
                                    $table AS parent
                                    WHERE node.$leftcol BETWEEN parent.$leftcol AND parent.$rightcol
                                    AND node.kode_unit = '{$node['kode_unit']}'
                                    GROUP BY node.kode_unit, node.nama,node.$leftcol
                                    ORDER BY node.$leftcol
                                )AS sub_tree
                                WHERE node.$leftcol BETWEEN parent.$leftcol AND parent.$rightcol
                                AND node.$leftcol BETWEEN sub_parent.$leftcol AND sub_parent.$rightcol
                                AND sub_parent.nama = sub_tree.nama
                                AND node.kode_unit != '{$node['kode_unit']}' 
                                GROUP BY node.kode_unit, node.nama, sub_tree.depth, node.$leftcol
                                ORDER BY node.$leftcol;";

                                    $query = $this->db->query($sql);
                                    $treeArray = array();

                                    if($query->num_rows() > 0) {
                                        foreach($query->result_array() AS $result) {
                                                $current_node = $this->getNodeFromId($result["kode_unit"]) ;
                                                $parent_node  = $this->getAncestor($current_node);
                                                if($result["depth"] == $depth) {
                                                        if($parent_node["kode_unit"] == $node["kode_unit"]) {
                                                                $treeArray[] = $result;
                                                        }
                                                }
                                        }
                                    }

                                    $retArray = array(  "result_array"  =>      $treeArray,
                            "prev_left"     =>      $node[$leftcol],
                            "prev_right"    =>      $node[$rightcol],
                            "level"         =>      -2);

                                    return $retArray;
                                     
        }
===END Kadaluarsa===

Referensi


  1. Nested sets, http://codeigniter.com/wiki/Nested_sets
  2. Nested sets with multiple root support, http://codeigniter.com/forums/viewthread/92540/
  3. Nested sets operation using Datamapper ORM, http://datamapper.wanwizard.eu/pages/extensions/nestedsets.html



Comments