CODEWITHSUNDEEP
mysqlsqltreehierarchy
Daniel Fath
Daniel Fath

Reputation: 18109

Nested joins on a naive tree set

Assuming I have something along the lines of this example:

CREATE TABLE NaiveTable
{
    id BIGINT NOT NULL, 
    parentId BIGINT NULL,
    name VARCHAR(20) NULL,
    CONSTRAINT hierarchy FOREIGN KEY (parentId) REFERENCES NaiveTable(id)
    PRIMARY KEY (id)
}

As a note parentId is a reference to id of NaiveTable (in case I missed the exact syntax).

The data are somewhere along the lines of these

+---------+----------+----------+
|   id    | parentId |  name    | 
+---------+----------+----------+
|   1     |   null   |  node1   |
+---------+----------+----------+
|   2     |     1    |  node2   |
+---------+----------+----------+
|   3     |     1    |  node3   |
+---------+----------+----------+
|   4     |     2    |  node4   |
+---------+----------+----------+

Column name contains some unrealated labels. I'm looking for a way to construct an SQL query on a MySQL table where all information will be flattened and sorted hierarchically like this:

node 1, depth 0
node 2, depth 1
node 4, depth 2
node 3, depth 1

NOTE: I can't modify the database schema in any way. I can only create SQL queries. Also I can't use WITH keyword since MySQL doesn't support it. Is there a way to make such query? However, any solution to depth two or beyond is considered good enough.

EDIT: Here is SQL fiddle if you love to experiment :)

Upvotes: 4

Views: 381

Answers (2)

jmilloy
jmilloy

Reputation: 8365

Here is a little recursive stored procedure, which should work to any depth. It's my first stored procedure, so please let me know how to improve it.

DROP PROCEDURE IF EXISTS tree_reader;
DELIMITER $$
CREATE PROCEDURE tree_reader(IN parent INT, IN depth INT) READS SQL DATA
BEGIN

  DECLARE id_val INT;
  DECLARE name_val VARCHAR(255);
  DECLARE _table_name VARCHAR(255);

  DECLARE no_more_rows BOOLEAN;
  DECLARE cur CURSOR FOR
      SELECT id, name
      FROM tree
      WHERE IF(parent IS NULL, parentid IS NULL, parentid = parent);

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

  -- create temporary table on outer call only --
  IF depth=0 THEN
    DROP TABLE IF EXISTS _tree;
    CREATE TEMPORARY TABLE _tree (id INT, name VARCHAR(255), depth INT);
  END IF;

  OPEN cur;

  -- loop with recursion --
  tree_loop: LOOP
    FETCH cur INTO id_val, name_val;
    IF no_more_rows THEN LEAVE tree_loop; END IF;
    INSERT INTO _tree VALUES (id_val, name_val, depth);
    CALL tree_reader(id_val, depth + 1);

  END LOOP tree_loop;

  CLOSE cur;

  -- output results on outer call only --
  IF depth=0 THEN
    SELECT * FROM _tree;
    DROP TABLE _tree;
  END IF;
END
$$
DELIMITER ;

A few notes:

  • Call the procedure: CALL tree_reader(NULL, 0);

  • You can use any parent node id, but the second argument is always 0. In practice, I'd probably add a wrapper procedure that takes no arguments and gives the whole tree.

  • You must set the recursion limit for it to work: SET max_sp_recursion_depth = 6; (I chose 6 arbitrarily).

Upvotes: 0

Mehran
Mehran

Reputation: 16901

If the database's schema is fixed and you can not add/edit any table, all you can do is to construct the tree in memory (in some programming language) and then try to calculate each node's depth in memory. So my answer is that you can not produce your desired output with just one query!

But if you could modify the schema of your database then you might want to check this out.

Upvotes: 1

Related Questions