sql complex tree rows

Question

i have following function with a query. it works fine with integer id, but if the id is varchar (ID: Xewi3adc), then it dont work. well i dont know how to make it work yet, i been making lots of modifications, but i could'nt get it work.

DROP FUNCTION IF EXISTS album_tree_connect;

DELIMITER $$

CREATE FUNCTION album_tree_connect(value varchar(32)) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE _id varchar(32);
    DECLARE _parent varchar(32);
    DECLARE _next INT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

    SET _parent = @id;
    SET _id = -1;

    IF @id IS NULL THEN
     RETURN NULL;
    END IF;

    LOOP
     SELECT MIN(album_id)
     INTO @id
     FROM album
     WHERE sub_album_of_album_id = _parent
      AND album_id > _id;
     IF @id IS NOT NULL OR _parent = @start_with THEN
      SET @level = @level + 1;
      RETURN @id;
     END IF;
     SET @level := @level - 1;
     SELECT album_id, sub_album_of_album_id
     INTO _id, _parent
     FROM album
     WHERE album_id = _parent;
    END LOOP; 
END
$$

DELIMITER ;


## select statement to pull the tree menu

SELECT   CONCAT(REPEAT('    ', level - 1), CAST(hi.album_id AS CHAR)) AS treeitem, sub_album_of_album_id, name, user_id, level
FROM    (
        SELECT  album_tree_connect(album_id) AS id, @level AS level
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0
                ) vars, album
        WHERE   @id IS NOT NULL
        ) ho
JOIN    album hi
ON      hi.album_id = ho.id
where user_id = 2

Answer 1

Your function return type is declared as INT, so the return value will be coerced to that type. If the value to be returned (the album_id field) is not an integer, it may get mangled. http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Answer 2

First, fix your function declaration as GApple suggested:

CREATE FUNCTION album_tree_connect(value varchar(32)) RETURNS VARCHAR(32)

Second, the initial _id should be initialized to an emtpy string , not -1:

SET _id = '';

Third, your @start_with should also be initialized with a string (the one that serves as a parent node identifier):

SELECT   CONCAT(REPEAT('    ', level - 1),
         CAST(hi.album_id AS CHAR)) AS treeitem, sub_album_of_album_id, name, user_id, level
FROM    (
        SELECT  album_tree_connect(album_id) AS id, @level AS level
        FROM    (
                SELECT  @start_with := '0',

                        --- The '0' above should be a string.

                        @id := @start_with,
                        @level := 0
                ) vars, album
        WHERE   @id IS NOT NULL
        ) ho
JOIN    album hi
ON      hi.album_id = ho.id

Note that the filter on user_id in the query above will most probably work incorrectly.

You need either to wrap the function into a subquery:

SELECT  *
FROM    (
        SELECT   CONCAT(REPEAT('    ', level - 1),
                 CAST(hi.album_id AS CHAR)) AS treeitem, sub_album_of_album_id, name, user_id, level
        FROM    (
                SELECT  album_tree_connect(album_id) AS id, @level AS level
                FROM    (
                        SELECT  @start_with := '0',

                                --- The '0' above should be a string.

                                @id := @start_with,
                                @level := 0
                        ) vars, album
                WHERE   @id IS NOT NULL
                ) ho
        JOIN    album hi
        ON      hi.album_id = ho.id
        ) q
WHERE   user_id = 2

or modify the hierarchical function to take the filter into account.