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

gapple · Accepted Answer · 2009-08-14 17:22:48Z

3

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

answered Aug 14, 2009 at 17:22

gapple

3,49324 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Basit Over a year ago

i tried.. but didnt work sadly.. can you try it and tell me please.. i can give you quick sql dump, if you want.

Quassnoi · Accepted Answer · 2009-08-17 10:01:03Z

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.

Collectives™ on Stack Overflow

sql complex tree rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related