Diabl0

Ostatnio zaprezentowałem MySQLową wersję implementacji drzewa tzw. metodą Depesza (patrz tutaj). Nie napisałem natomiast nic na temat praktycznego wykorzystania takiego drzewa. Dlatego dzisiaj ciąg dalszy, tym razem już bardziej praktyczny.

Aby wygodnie operować na naszej bazie, a zwłaszcza znacznie ułatwić sobie pobieranie list kategorii gotowych do wyświetlenia, potrzebujemy jeszcze 2 funkcji:

1 – ścieżka kategorii – Potrzebna nam do przygotowania posortowanej listy kategorii:

DROP FUNCTION IF EXISTS treepath;
CREATE FUNCTION treepath( in_leaf_id INTEGER ) RETURNS TEXT
BEGIN
	--
	-- Funckcja zwracająca ścieżkę kategorii dla zadanego ID kategorii
	--
	DECLARE reply TEXT;
	DECLARE t_name VARCHAR(127);

	-- deklarujemy kursor (zakręcona implementacja pgsqlowego FOR row IN query LOOP)
	DECLARE done INT DEFAULT 0;
	DECLARE cur1 CURSOR FOR
		SELECT tree.name FROM tree INNER JOIN tree_pos ON tree.id = tree_pos.parent_id WHERE tree_pos.tree_id =  in_leaf_id ORDER BY tree_pos.depth DESC;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	OPEN cur1;
		REPEAT
			FETCH cur1 INTO t_name;
			IF NOT done THEN
					SET reply = CONCAT_WS( '/', reply, t_name );
			END IF;
		UNTIL done END REPEAT;
	CLOSE cur1;

	SET reply = CONCAT( '/', reply );

    RETURN reply;
END;

Przykład działania:

mysql> select treepath(8);
+-------------------------------------------+
| treepath(8)                               |
+-------------------------------------------+
| /Kategoria główna/Kat 1/Kat 1-2/Kat 1-2-1|
+-------------------------------------------+
1 row in set

2 – poziom zagnieżdżenia wybranej kategorii

DROP FUNCTION IF EXISTS item_depth;
CREATE FUNCTION item_depth( in_item_id INTEGER) RETURNS INTEGER
BEGIN
	--
	-- Funkcja zwraca poziom zagnieżdżenia dla zadanego ID kategorii
	--
	DECLARE reply INTEGER;

	SELECT depth INTO reply FROM tree_pos WHERE tree_id = in_item_id ORDER BY depth DESC LIMIT 1;

    RETURN reply;
END;

Ta natomiast banalna i pozornie niepotrzebna funkcja znacznie ułatwi nam zbudowanie podstawowego zapytania:

mysql> SELECT *, item_depth( id ) AS depth FROM tree ORDER BY treepath(id) ASC;
+----+-----------+------------------+-------+
| id | parent_id | name             | depth |
+----+-----------+------------------+-------+
|  1 | NULL      | Kategoria główna |     0 |
|  2 |         1 | Kat 1            |     1 |
|  4 |         2 | Kat 1-1          |     2 |
|  7 |         4 | Kat 1-1-1        |     3 |
|  9 |         4 | Kat 1-1-2        |     3 |
|  5 |         2 | Kat 1-2          |     2 |
|  8 |         5 | Kat 1-2-1        |     3 |
|  3 |         1 | Kat 2            |     1 |
|  6 |         3 | Kat 2-1          |     2 |
+----+-----------+------------------+-------+
9 rows in set

lub idąc dalej:

mysql> SELECT id, CONCAT( REPEAT('  ', item_depth( id ) ), '+- ', name) FROM tree ORDER BY treepath(id) ASC;
+----+-------------------------------------------------------+
| id | CONCAT( REPEAT('  ', item_depth( id ) ), '+- ', name) |
+----+-------------------------------------------------------+
|  1 | +- Kategoria główna                                 |
|  2 |   +- Kat 1                                            |
|  4 |     +- Kat 1-1                                        |
|  7 |       +- Kat 1-1-1                                    |
|  9 |       +- Kat 1-1-2                                    |
|  5 |     +- Kat 1-2                                        |
|  8 |       +- Kat 1-2-1                                    |
|  3 |   +- Kat 2                                            |
|  6 |     +- Kat 2-1                                        |
+----+-------------------------------------------------------+
9 rows in set

Z tak przygotowanymi danymi już chyba każdy sobie poradzi z ich wyświetlaniem.

Garść innych ciekawych informacji (jak np. modyfikacja kolejności) znajdziecie na stronie http://www.depesz.com/various/various-sqltrees-implementation.php z której czerpałem inspiracje.

Uzupełniony zrzut z testowej bazy łącznie z przykładowymi wpisami można znaleźć tutaj: tree_v2.1.sql. Natomiast sam opis struktury i triggerów znajdziecie tutaj.

Tags: Depesz, drzewo, metoda Depesza, MySQL, SQL, tree, trigger

This entry was posted on piątek, Marzec 20th, 2009 at 11:03 and is filed under MySQL, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.