CODEWITHSUNDEEP
mysql
user4054093
user4054093

Reputation:

Mysql select recursive get all child with multiple level

i have a table 

 CREATE TABLE IF NOT EXISTS `Folder` (
    `idFolder` INT(11) NOT NULL AUTO_INCREMENT,
    `FolderName` VARCHAR(150) NOT NULL,
    `idFolderParent` INT(11) NULL,
    PRIMARY KEY (`idFolder`),
    CONSTRAINT `fk_1`
    FOREIGN KEY (`idFolderParent`)
    REFERENCES `Folder` (`idFolder`)
)

i fill this table by

idFolder , FolderName , idFolderParent
   1           ADoc           Null  
   2           ADoc1           1  
   3           ADoc2           2
   4           ADoc3           3
   5           ADoc4           4
   6           ADoc5           5
   7           ADoc6           4

when select a folder with idFolder=1, it should be return all child for this folder and subchild folder(2, 3, 4, 5 ,6 ,7)

When i select a folder id = 4 ==> (5,7, 6)

When i select a folder id = 3 ==> (4, 5,6, 7)

How to do that with one query?

Thx

Upvotes: 17

Views: 43072

Answers (7)

sam
sam

Reputation: 31

You can use 'ORDER BY nodes' to get an ordered tree:

CREATE TABLE `companies` (
  `id` bigint UNSIGNED NOT NULL,
  `parent_id` bigint UNSIGNED NOT NULL DEFAULT '0',
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL
) ;

ALTER TABLE `companies`
  ADD PRIMARY KEY (`id`);

INSERT INTO `companies` (`id`, `parent_id`, `name`) VALUES
(1, 9, 'B1'),
(3, 9, 'B2'),
(4, 9, 'B3'),
(5, 1, 'B1C1'),
(6, 3, 'B2C1'),
(7, 3, 'B2C2'),
(8, 7, 'B2C2D1'),
(9, 0, 'ROOT'), -- This is the root node
(11, 9, 'B4'),
(13, 5, 'B1C1D1');

WITH RECURSIVE treeview (id, parent_id, name, lvl, nodes) AS
(
  SELECT id, parent_id, name, 0, CAST('0' AS CHAR(10))
    FROM `companies`
    WHERE parent_id = 0
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.lvl + 1, CONCAT(nodes, ',', c.id)
    FROM treeview AS t
    INNER JOIN `companies` AS c
      ON t.id = c.parent_id
)

SELECT id, CONCAT(REPEAT('  . ', lvl), name), lvl, parent_id, nodes
FROM treeview
ORDER BY nodes

And this is the output:

Hierarchical structure

Check the SQLFiddle

Upvotes: 0

Sachin Sarola
Sachin Sarola

Reputation: 1041

May be it works if you don't want to main id in result remove SELECT 4 Level UNION from query

SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM ( SELECT 4 Level UNION SELECT @Ids := (SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM folder WHERE FIND_IN_SET(idFolderParent, @Ids)) Level FROM folder JOIN (SELECT @Ids := 4) temp1 ) temp2

Look at SQL FIddle here : http://sqlfiddle.com/#!9/a2b4b3/312

Upvotes: 3

Manoj Rana
Manoj Rana

Reputation: 3440

to get all level child of a particular parent you can use this below query:

 select idFolder from (select * from Folder order by idFolderParent, idFolder) Folder, (select @pv := '1') initialisation where find_in_set(idFolderParent, @pv) > 0 and @pv := concat(@pv, ',', idFolder )

Upvotes: -1

Oğuz Can Sertel
Oğuz Can Sertel

Reputation: 759

select  idFolder, FolderName, idFolderParent
                                from (select * from Folder order by idFolderParent, idFolder) folders_sorted,
                                (select @pv := 1) initialisation
                                where   find_in_set(idFolderParent, @pv) > 0
                                and @pv := concat(@pv, ',', idFolder)

@pv := 1 is your current Folder id.. I think this is much better solution.

Dheerendra Kulkarni's answer which is below gives me this error for different collations. I hope this will helps someone.

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
    SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder WHERE idFolderParent IN (@pv)) AS lv FROM Folder
    JOIN
    (SELECT @pv:=1)tmp
    WHERE idFolderParent IN (@pv)) a;

Upvotes: 3

Adrián E
Adrián E

Reputation: 1681

None of the previous solutions worked for me. Both only work if the parents are saved into the database in a certain order.

I have to admit I do not fully understand the way the query works but could find a way that works for me (at least better than the other answers).

The data with which the first and second queries do not work is:

idFolder , FolderName , idFolderParent
   1           ADoc           Null  
   2           ADoc1           7  
   3           ADoc2           2
   4           ADoc3           3
   5           ADoc4          Null
   6           ADoc5           5
   7           ADoc6           5

If you use the first and second queries in this dataset, for the id 5 you only get as a result '6,7'. But if you use my query you get: '6,7,2,3,4' which is the expected result.

My version:

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder 
WHERE FIND_IN_SET(idFolderParent, @pv)) AS lv FROM Folder 
JOIN
(SELECT @pv:=5) tmp
) a;

Hope it helps someone. I cannot comment nor downvote the other answers because of lack of reputation :(

Upvotes: 24

asceta
asceta

Reputation: 283

Be aware the MySQL treats the

idFolderParent IN ('1, 2')

as a single value, so it is equal to:

idFolderParent IN ('1')

so in order to operate on the list, you need to:

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder WHERE
FIND_IN_SET(idFolderParent, @pv)) AS lv FROM Folder
JOIN (SELECT @pv:=1)tmp
WHERE idFolderParent IN (@pv)) a;

(the FIND_IN_SET)

Upvotes: 8

Dheerendra Kulkarni
Dheerendra Kulkarni

Reputation: 2744

Here is the working one

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder WHERE idFolderParent IN (@pv)) AS lv FROM Folder
JOIN
(SELECT @pv:=1)tmp
WHERE idFolderParent IN (@pv)) a;

Look at SQL FIddle here:http://sqlfiddle.com/#!2/02b78/1

Upvotes: 17

Related Questions