Get all possible parent ids only if has parent bigger than 0

Posted on

Question :

Right now If I call get_parent_id(4) I will get all id parents. This means 3,2,1. Is not hard to guess that 1 has parent 0.

This is my problem. I want to output all ids unless the parent is 0. To do this I have one more query (check_for_parent_0) in this function.

Please suggest any solution so I can have only one query and not two in the next function. Any other advices for a better performance is welcomed.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_parent_id`(`id` INT) RETURNS varchar(1024) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE return_string VARCHAR(1024);
    DECLARE return_string_separator CHAR(1);
    DECLARE parent_id INT;
    DECLARE check_for_parent_0 INT;

    SET return_string = '';
    SET return_string_separator = '';
    SET parent_id = id;
    SET check_for_parent_0 = '';

    WHILE parent_id > 0 DO

        SET parent_id = (
            SELECT
                forum_parent_id
            FROM
                forums
            WHERE
                forum_id = parent_id
        );

        SET check_for_parent_0 = (
            SELECT forum_parent_id FROM forums WHERE forum_id = parent_id AND forum_parent_id != 0
        );

        IF (parent_id > 0 AND check_for_parent_0 > 0) THEN
            SET return_string = CONCAT(return_string, return_string_separator, parent_id);
            SET return_string_separator = ',';
        END IF;

    END WHILE;

    RETURN return_string;
END$$

Answer :

I have a way to do it in one query, though it may not be faster than your current solution.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_parent_id`(`id` INT) RETURNS varchar(1024) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE return_string VARCHAR(1024);
    DECLARE return_string_separator CHAR(1);
    DECLARE parent_id INT;

    SET return_string = '';
    SET return_string_separator = '';
    SET parent_id = id;

    WHILE parent_id > 0 DO

        SET parent_id = (
            SELECT
                forum_parent_id
            FROM
                forums
            WHERE
                forum_id = parent_id and forum_parent_id not in (Select forum_id from forums where forum_parent_id=0)
        );

        IF (parent_id > 0) THEN
            SET return_string = CONCAT(return_string, return_string_separator, parent_id);
            SET return_string_separator = ',';
        END IF;

    END WHILE;

    RETURN return_string;
END$$

Leave a Reply

Your email address will not be published.