Snippet for post Hierarki data dalam satu tabel

sample.sql

-- v2, mengubah nama tabel dan kolom ke bahasa inggris
-- dan re-struktur nama variabel di prosedur
-- dan menambahkan kolom depth (kedalaman), agar lebih mudah melihat kedalaman element tsb
--     tanpa harus melakukan group terhadap parent-nya, karena grouping membuat query sangat lama
--     untuk data yang lebih jumlahnya ribuan


-- Query#1
-- buat tabel kategori (hanya sample)
DROP TABLE IF EXISTS category;
CREATE TABLE category (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -- untuk mempercepat pencarian parent / sibling, gunakan kode
    code VARCHAR(10) NOT NULL UNIQUE,
    -- jika nama digunakan dalam query pencarian, tambahkan index supaya lebih efisien    
    name VARCHAR(50) NOT NULL,
    -- nilai kiri
    lft INT NOT NULL,
    -- nilai kanan
    rgt INT NOT NULL,
    -- kedalaman
    depth INT NOT NULL
);

-- Query#2
-- ini untuk contoh penggunaan aggregate function seperti count, average dsb
DROP TABLE IF EXISTS product;
CREATE TABLE product
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40),
    -- kita gunakan kode kategori sebagai ganti id, dalam implementasi lebih baik gunakan id untuk relasi
    category_code VARCHAR(10) NOT NULL
);

-- Query#3
-- buat prosedure supaya lebih mudah insert node
DELIMITER $$

DROP PROCEDURE IF EXISTS `add_node` $$

CREATE PROCEDURE `add_node`(sCode VARCHAR(10), sName VARCHAR(255), sParent VARCHAR(10))
BEGIN
SET @ssParent = sParent;
	SET @ssName = sName;
	SET @ssCode = sCode;
    SET @myLeft = 0;
    SET @myDepth = -1;
	SET @findParentQuery = 'SELECT lft, depth INTO @myLeft, @myDepth FROM category WHERE code = ?';
    SET @insertChildQuery = 'INSERT INTO category(code, name, depth, lft, rgt) VALUES(?, ?, @myDepth + 1, @myLeft + 1, @myLeft + 2)';
        
    PREPARE stmt FROM @findParentQuery;
    EXECUTE stmt USING @ssParent;
    DEALLOCATE PREPARE stmt;

	UPDATE category SET rgt = rgt + 2 WHERE rgt > @myLeft;
	UPDATE category SET lft = lft + 2 WHERE lft > @myLeft;
    
    PREPARE stmt FROM @insertChildQuery;
    EXECUTE stmt USING @ssCode, @ssName;
    DEALLOCATE PREPARE stmt;
END$$

-- Query#4
-- procedure remove node
DELIMITER $$
DROP PROCEDURE IF EXISTS `remove_node` $$

CREATE PROCEDURE `remove_node`(sCode VARCHAR(10))
BEGIN
	SET @ssCode = sCode;
	SET @findNodeQuery = 'SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 FROM category WHERE code = ?';
        
    PREPARE stmt FROM @findNodeQuery;
    EXECUTE stmt USING @ssCode;
    DEALLOCATE PREPARE stmt;

    DELETE FROM category WHERE lft BETWEEN @myLeft AND @myRight;

    UPDATE category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
    UPDATE category SET lft = lft - @myWidth WHERE lft > @myRight;
END$$

DELIMITER ;

-- Query#5
-- data sampel (tentang komputer dan aksesorisnya)
-- supaya tidak error clear data dulu
DELETE FROM product;
DELETE FROM category;

-- root-nya, biar masuk akal
-- ingat, argument pertama dan kedua kode dan nama kategori nya, argument terakhir adalah kode kategori parent-nya
CALL add_node('000', 'root', null);

-- node laptop dan sub nya (saya memakai name merek biar mudah, bukan promosi ya)
CALL add_node('100', 'laptop', '000' /* ini adalah code untuk root, jadi laptop ini anak dari root */);
CALL add_node('110', 'asus', '100' /* ini adalah code laptop, tau kan artinya :D */);
-- misal asus ini punya beberapa tipe
CALL add_node('111', 'asus laptop', '110');
CALL add_node('112', 'asus notebook', '110');
CALL add_node('120', 'acer', '100');
CALL add_node('130', 'samsung', '100');
CALL add_node('140', 'lenovo', '100');

CALL add_node('200', 'desktop computer', '000');
CALL add_node('210', 'monitor', '200');
CALL add_node('211', 'lcd 16 in', '210');
CALL add_node('212', 'lcd 20 in', '210');
CALL add_node('220', 'casing', '200');
CALL add_node('300', 'aksesoris', '000');
-- selebihnya bisa dicoba sendiri

-- sampel data untuk product
INSERT INTO product (name, category_code) VALUES
('Asus A45VD','111'),
('Asus A45XD','111'),
('Monitor Samsung','211'),
('Monitor AOC','211'),
('Charger ORI','300');

-- Query#6
-- berikut adalah contoh pengambilan datanya
-- full tree
SELECT node.name
FROM category AS node,
        category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'root' /* ini bisa diganti untuk mencari dari parent yang lain
        atau bisa menggunakan pencarian berdasarkan code yang lebih akurat
        */
ORDER BY node.lft;

-- Query#7
-- all leaf node (ambil data yang tidak memiliki child)
SELECT name
FROM category
WHERE rgt = lft + 1; /* simpel kan, ini karena nilai rgt child node (harus) selalu bernilai nilai lft plus satu */

-- Query#8
-- depth of the nodes (kedalaman node, ini untuk keseluruhan tree, untuk mencari kedalaman dari sub tree, silahkan lihat dibawah)
-- *sudah tidak perlu : kedalaman node, berdasarkan nilai dari column depth yang diisi saat add node*
/*
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM category AS node,
        category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.rgt
ORDER BY node.lft;
*/

-- Query#9
-- menggunakan kedalaman node untuk mem-format tampilan (di sql)
SELECT CONCAT( REPEAT('    ', node.depth), node.name) AS name
FROM category AS node,
        category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.name = 'root'
ORDER BY node.lft;

-- Query#10
-- kedalaman sub-tree 
-- tidak diperlukan lagi, karena sudah menggunakan explicit depth pada saat insert data
/*
SELECT node.name, (parent.depth - sub_tree.depth) AS depth
FROM category AS node,
        category AS parent,
        category AS sub_parent,
        (
                SELECT node.name, node.depth
                FROM category AS node,
                category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = 'asus'
                ORDER BY node.lft
        ) AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
ORDER BY node.lft;
*/

-- Query#11
-- immediate sub ordinate of a node (cari yang anaknya langsung tanpa mengambil sub di bawahnya)
-- misal dibawah ini kita hanya akan mengambil sub category yang langsung turunan dari desktop computer
/*
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM category AS node,
        category AS parent,
        category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM category AS node,
                        category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.name = 'desktop computer'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;
*/
-- sekarang menggunakan nilai dari kolom depth
SELECT node.name
FROM category AS node,
        category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'root' /* ini bisa diganti untuk mencari dari parent yang lain
        atau bisa menggunakan pencarian berdasarkan code yang lebih akurat
        */
        AND parent.depth = node.depth - 1
ORDER BY node.lft;

-- Query#12
-- contoh aggregate function
SELECT parent.name, COUNT(product.name)
FROM category AS node ,
        category AS parent,
        product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.code = product.category_code
GROUP BY parent.name
ORDER BY node.lft;

-- Query#13
-- contoh penggunaan remove_node untuk menghapus node
CALL remove_node('100' /* ini adalah code untuk category laptop, laptop dan seluruh anaknya akan terhapus */ );
-- untuk validasi hasil pemanggilan prosedur ini bisa menggunakan Query#9

-- sekian, terima kasih
-- ---
-- Eko Kurniawan