2

I am working on a table referrals which contains parent child relations

enter image description here

I need to get the parent -> children -> ther children -> ....

for the above table data my desired result is

enter image description here

I have seen some code from SOF but didn't get how they are working and tried myself with a very easy logic in my mind but unfirtunately its not working for a strange reason

I have written Stored Procedure for it but am stuck with an issue with the IN CLAUSE

DELIMITER $$

DROP PROCEDURE IF EXISTS `GetHierarchy3`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetHierarchy3`()
BEGIN
    DECLARE idss VARCHAR(225);
    SET @currentParentID := 999999;
    SET @lastRowCount := 0;

  ## A ##   
        INSERT INTO referrals_copy SELECT * FROM referrals WHERE uid1 = @currentParentID;

    SET @lastRowCount := ROW_COUNT();

 ## B ##
        SELECT GROUP_CONCAT(uid2)  INTO @idss FROM referrals WHERE  uid1 = @currentParentID;


    #SELECT @lastRowCount;
    SELECT * FROM referrals_copy;

    WHILE @lastRowCount > 0 DO
    SELECT "here";

        SELECT @idss;
  ## C ##
            INSERT INTO referrals_copy SELECT uid1, uid2 FROM referrals 
                WHERE uid1 IN (@idss);
            SET @lastRowCount := ROW_COUNT();
        #set @ids := NULL;
        SELECT @idss;
        SELECT GROUP_CONCAT(uid2) FROM referrals WHERE  uid1 IN (@idss);
        SELECT @idss;
        SET @lastRowCount := 0;

    END WHILE;


    -- return the final set now
    SELECT 
            *
        FROM referrals_copy;

END$$

CALL GetHierarchy3();

first I get the children for the main parent I want in the first query A, working fine

then, gets the children of main parent into variable @idss query B, working fine

then in the loop I use the variable idss in the query to get its children query C and put them in the resultant table... This is the problematic part

here for the very first iteration I have 1111,2222 in the variable idss which should result in insertion of values {3333, 4444} child of 1111, and {5555} child of 2222 in the table but it only put the children of 1111 ie 3333,4444 but, not 5555

I tried to replace variable from query C to check and it worked fine with values 1111,2222' withinIN Claues`

any idea on why the IN Clause is not accepting comma separated values from a group concat or any idea on fixing this code

Regards

Junaid
  • 2,084
  • 1
  • 20
  • 30
  • MySQL doesn't support recursive functions, so it is not well suited to this adjacency list model of storing hierarchical data. You ought to consider restructuring your data to use either nested sets or closure tables. See [this answer](http://stackoverflow.com/a/192462/623041) for more information. – eggyal Jun 11 '12 at 09:59
  • 2
    This is important to note... `SELECT * FROM x WHERE y IN (1,2,3)` is fundamentally different from `SELECT * FROM x WHERE y IN ('1,2,3')`. In the first case there are 3 integer items, but in second case there is only ONE item, and that item is a string. MySQL does NOT treat that string as a list of numbers; you have a list of strings, with just 1 item. To convert a string into a list of integers you need to look up a `split` function; there are many on the net. Such a function could be used in queries similar to: `SELECT * FROM x WHERE y IN ((SELECT * FROM fn_split('1,2,3') AS list))`. – MatBailie Jun 11 '12 at 10:03

1 Answers1

4

Try this FIND_IN_SET(uid1,@idss)

You have to replace this query part

SELECT GROUP_CONCAT(uid2) FROM referrals WHERE uid1 IN (@idss);

into this

SELECT GROUP_CONCAT(uid2) FROM referrals WHERE FIND_IN_SET(uid1,@idss);

i hope its work for you....

Query Master
  • 6,989
  • 5
  • 35
  • 58
  • hey it worked man! my bad i never heard of this function.. just one concern; the performance of the query .. google-ing about that. if you can share some links for that it might help me more ... Regards :) – Junaid Jun 11 '12 at 11:56