for example if I provide idCategory=8 which is “black”, it should return some thing like
6,4,2,1 which is (Jeans->Trouser->Mens Wear-> Clothes)
but unfortunately I am unable to write such query, so I decided to write a stored procedure or a function that full fill the purpose. both options were open, so I started with stored procedure(as debugging is easy compared to function) and finally converted it to function to full fill my requirements.
Note: There are 2 ways to do it, first one is to use Recursive one, which is easy to code/write and second one is iterative way, so I decided the 2nd method.
This stored procedure takes 2 parameters
1st is the id of the category
2nd is the maximum depth level, there are chances that we might have a bug or some thing in our code and procedure get stuck in infinite loop or there is also possibility there is a circular loop of categories. (like A is parent of B, B is parent of C, and C is parent of A) A->B->C->A
so 2nd parameter will check the maximum allowed iteration.
here is procedure
DROP PROCEDURE IF EXISTS getAllParentCategories; CREATE PROCEDURE `getAllParentCategories`( IN idCat int, IN intMaxDepth int) BEGIN DECLARE chrProcessed TEXT; DECLARE quit INT DEFAULT 0; DECLARE done INT DEFAULT 0; DECLARE Level INT DEFAULT 0; DECLARE idFetchedCategory INT; DECLARE chrSameLevelParents VARCHAR(255); DECLARE chrFullReturn VARCHAR(255); DECLARE cur1 CURSOR FOR SELECT idParentCategory FROM tbl_categories WHERE idCategory IN (@param); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET chrFullReturn = ''; SET @param = idCat; set chrProcessed = concat('|',idCat, '|'); myloop:LOOP IF quit = 1 THEN leave myloop; END IF; OPEN cur1; SET chrSameLevelParents = ''; FETCH cur1 INTO idFetchedCategory; while(not done) do SET Level = Level + 1; IF idFetchedCategory > 0 THEN if NOT INSTR(chrProcessed,concat('|',idFetchedCategory, '|')) > 0 THEN if CHAR_LENGTH(chrSameLevelParents) > 0 then set chrSameLevelParents = concat( idFetchedCategory, ',', chrSameLevelParents ); else set chrSameLevelParents = idFetchedCategory; end if; set chrProcessed = concat('|',idFetchedCategory, '|', chrProcessed ); end if; END IF; FETCH cur1 INTO idFetchedCategory; end while; CLOSE cur1; IF Level > intMaxDepth THEN SET done =1; SET quit = 1; END IF; if CHAR_LENGTH(chrSameLevelParents) > 0 THEN if CHAR_LENGTH(chrFullReturn) > 0 THEN set chrFullReturn = concat( chrFullReturn, ',', chrSameLevelParents ); ELSE set chrFullReturn = chrSameLevelParents; END IF; SET @param = chrSameLevelParents; SET chrSameLevelParents = ''; SET done = 0; ELSE SET quit = 1; END IF; END LOOP; SELECT Level,chrFullReturn; END;
Now we will call this function as follows
call getAllParentCategories(8 ,100);
8 is idCategory of “Black” and 100 is max depth.
result set will look like as follows
Here problem is chrFullReturn is text value and will not produce any use full result however if this procedure is called from any programming language then we can use this result in another query to get categories name.
or we can replace following query with prepare statement to retrieve proper result set.
SET @strQuery = concat('SELECT idCategory, CategoryName FROM tbl_categories WHERE idCategory IN (',chrFullReturn,')'); PREPARE stmt1 FROM @strQuery; EXECUTE stmt1; DEALLOCATE PREPARE stmt1;
Now it will return proper and useful result set.