Mysql Function for parent child category

Question

I have table name pctable for parent child category.

  id      parent_id
  2322    0
  2323    2322
  2324    2322
  2335    2322
  2336    2322
  2337    2322
  4869    2322
  5121    2322
  6033    2322
  6783    2322
  1061    2323
  4870    4869
  4871    4869
  4872    4869
  4873    4869
  6034    6033
  6059    6033

I wrote a mysql function to get all parent child in comma separated string.

DELIMITER $$
    DROP FUNCTION IF EXISTS getBaseID $$
    CREATE FUNCTION getBaseID(articleID varchar(1024)) RETURNS TEXT
    BEGIN
        DECLARE x TEXT;
        DECLARE y TEXT;
        DECLARE rtext TEXT;
        SET rtext = "";
        SET x = articleID;
        sloop:LOOP
            SET y = NULL;
            SELECT SQL_CACHE GROUP_CONCAT(id) INTO y FROM pctable WHERE parent_id IN(x);
            IF y IS NULL THEN
                LEAVE sloop;
            END IF;  
            SET x = y;
            SET rtext = CONCAT(rtext,',',x);
            ITERATE sloop;
        END LOOP;
        RETURN rtext;
    END $$

    DELIMITER ;

When I am calling function it is returning wrong data.

  SELECT getBaseID(2322) FROM pctable LIMIT 1;

It's returning

 "2323,2324,2335,2336,2337,4869,5121,6033,6783,1061"

But it should return

  "2323,2324,2335,2336,2337,4869,5121,6033,6783,1061,4870,4871,4872,4873,6034,6059"

Answer 1

The problem with your code is, that this

WHERE parent_id IN(x);

when x is replaced with its value actually becomes this:

WHERE parent_id IN(',2323,2324,2335,2336,2337,4869,5121,6033,6783,1061');

Note the single-quotes in there. You think you pass a series of numbers, but in fact you pass one single string of text. Also note, that with your code there's this comma in front. So even when it wouldn't pass one single string of text, it would fail because of a syntax error. What you need to do is to use dynamic sql. This is only possible in stored procedures, not functions.

I've rewritten your code for you:

DELIMITER $$
DROP PROCEDURE IF EXISTS getBaseID $$
CREATE PROCEDURE getBaseID(IN articleID varchar(1024), OUT rtext TEXT)
BEGIN
    SET rtext = "-1";
    SET @x = articleID;
    sloop:LOOP
        SET @y = NULL;
        SET @sql = CONCAT('SELECT GROUP_CONCAT(id) INTO @y FROM pctable WHERE parent_id IN(', @x, ');');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        IF @y IS NULL THEN
            LEAVE sloop;
        END IF;  
        SET @x = @y;
        SET rtext = CONCAT(rtext, ',', @x);
        ITERATE sloop;
    END LOOP;
END $$

DELIMITER ;

I added the

    SET rtext = "-1";

to overcome the problem with the leading comma, assuming that you have no negative ids in your table.

You would execute it with

CALL getBaseID(2322, @my_result); /*This executes the function and writes the result into the variable you pass as second parameter.*/
SELECT @my_result;                /*Then you select the result.*/

• see it working live in an sqlfiddle

And as a last note, even if it would work with your function, you wouldn't need to select your table in this case. You could execute it simply with SELECT getBaseID(2322); Nothing in this function refers to a row or something.

Answer 2

For the first loop it will find all records where parent_id is IN ("2322") which gets you 2323,2324,2335,2336,2337,4869,5121,6033 and 6783. The next loop it will find all the records where parent_id is IN ("2323,2324,2335,2336,2337,4869,5121,6033,6783"). In basic theory this will find nothing, but I think as it is comparing an INT to a CHAR it will convert the CHAR to an INT, hence just taking everything up to the first comma and effectively looking for IN ("2323") which returns 1061. As 1061 is not the parent of any records this means y is null on the next time around the loop so it drops out.

Quick fix would be:-

DELIMITER $$
    DROP FUNCTION IF EXISTS getBaseID $$
    CREATE FUNCTION getBaseID(articleID varchar(1024)) RETURNS TEXT
    BEGIN
        DECLARE x TEXT;
        DECLARE y TEXT;
        DECLARE rtext TEXT;
        SET rtext = "";
        SET x = articleID;
        sloop:LOOP
            SET y = NULL;
            SELECT SQL_CACHE GROUP_CONCAT(id) INTO y FROM pctable WHERE FIND_IN_SET(parent_id, x);
            IF y IS NULL THEN
                LEAVE sloop;
            END IF;  
            SET x = y;
            SET rtext = CONCAT(rtext,',',x);
            ITERATE sloop;
        END LOOP;
        RETURN rtext;
    END $$

    DELIMITER ;