MariaDB 10.2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `position` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
  `first_name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `position` (`id`, `user_id`, `parent_id`, `uuid`, `type`) VALUES
(2, 3, NULL, '7db54d6a-6a89-3fe2-87ba-c0dd96d4f664', 'TYPE_HR_PLUS'),
(3, 4, 3, 'adadab97-c0b8-3f89-a245-4edb077a4579', 'TYPE_HR_PLUS'),
(4, 5, 3, '4efec484-8a1f-3c27-a48f-a8c8412e18db', 'TYPE_HR_PLUS'),
(5, 6, 4, '8027b0cb-3e34-357a-8bff-80e4abbf60ef', 'TYPE_HR_PLUS'),
(6, 7, 4, 'e588ec20-9b0e-3ec6-8164-bdfafdf4b440', 'TYPE_HR'),
(7, 8, 5, '61e537d7-5ed6-36b3-8969-1913cdacb42b', 'TYPE_HR'),
(8, 9, 5, '6c241f8f-1a0a-34dc-8042-591352a764ea', 'TYPE_HR_PLUS'),
(9, 9, 3, 'e03998c4-d95a-3410-9727-099f0c83b3d5', 'TYPE_COLLABORATOR'),
(10, 10, 6, '67de2883-a7b0-3260-bb2a-67d3ffa3dc40', 'TYPE_HR'),
(11, 11, 7, '07d37612-01bf-3eec-b249-41c20d756144', 'TYPE_COLLABORATOR'),
(12, 11, 5, '09f7f6f1-59e2-37c0-a2e4-ae9b58ba6c93', 'TYPE_HR'),
(13, 12, 9, '724b8185-b6eb-3d35-b71f-1d076b3020ed', 'TYPE_HR'),
(14, 13, 9, '97d90e5c-7872-3e1e-be65-0f6b5329f54c', 'TYPE_HR_PLUS'),
(15, 14, 10, '3c357b9d-e162-316b-921c-25157d523e70', 'TYPE_COLLABORATOR'),
(16, 14, 3, '542235c6-462c-4922-a629-303430701000', 'TYPE_COLLABORATOR'),
(17, 15, 10, '2d208936-a7e9-32c1-963f-0df7f57ae463', 'TYPE_COLLABORATOR'),
(18, 16, 11, '00b678a2-54cd-3f37-9381-0cd92acea079', 'TYPE_COLLABORATOR'),
(19, 17, 8, 'ec4b8cad-fbce-3692-8d4c-f48f7ffa6452', 'TYPE_COLLABORATOR'),
(20, 18, 12, '48f0c85c-3801-3275-8978-5b2573fd7e0b', 'TYPE_COLLABORATOR'),
(21, 18, 10, 'd6129dec-8823-3304-8dfe-819118073d1f', 'TYPE_COLLABORATOR'),
(22, 19, 13, '1ec9c202-97d7-3311-9119-f6e9f55d058b', 'TYPE_COLLABORATOR'),
(23, 20, 9, '3ab32120-520b-3d41-80f0-56fd3876eecb', 'TYPE_COLLABORATOR');
INSERT INTO `user` (`id`, `uuid`, `first_name`, `last_name`) VALUES
(3, 'fb7304f6-5ade-4c06-83e5-36827a4d7904', 'Albus', 'Dumbledore'),
(4, '60af55ca-e9cd-3d8a-bc8a-c9f7ec99baa1', 'Minerva', 'McGonagall'),
(5, 'a422b364-b68f-324b-8836-d38ca62001f5', 'Severus', 'Rogue'),
(6, 'cbdf94a8-f4e3-360b-ba7f-10ea7e59b85d', 'Filius', 'Flitwick'),
(7, '9bce88c9-7b7d-3db3-8780-b6397bb1263d', 'Remus', 'Lupin'),
(8, 'b9d85cfa-3c46-33c1-8750-53be1c281111', 'Pomona', 'Chourave'),
(9, 'c07d10d7-333f-327a-ab32-201209127b7a', 'Rolanda', 'Bibine'),
(10, '8a7b76a1-5f0c-31ff-a93a-ca478598682b', 'Gilderoy', 'Lockhart'),
(11, '7302700a-0bc5-3ed0-84fc-857e975a5771', 'Alastor', 'Maugrey'),
(12, '7b8722f3-1570-3351-864a-ede9ee3a90e6', 'Sybille', 'Trelawney'),
(13, '12a7683a-00da-46a6-b7fd-a6eae5b10e3c', 'Dolores', 'Ombrage'),
(14, 'f054bdeb-a8b8-41e5-be78-b00d1b9377f3', 'Harry', 'Potter'),
(15, 'b1f696ad-cfa3-3830-8685-c45ff4a4ffda', 'Ronald', 'Weasley'),
(16, '8b2a64e9-ed28-363d-a731-3e15e255cd1d', 'Hermione', 'Granger'),
(17, 'f6aa74f9-2a4a-346d-bf75-3f8b02614c19', 'Neville', 'Londubat'),
(18, '8fbbc431-2555-3718-8055-ad8637260732', 'Drago', 'Malefoy'),
(19, '78a58546-8ae1-330d-9426-c23c74b71e78', 'Luna', 'Lovegood'),
(20, '4739a44b-d1b7-3390-9bbb-cbd2e39b4ec0', 'Ginny', 'Weasley');
ALTER TABLE `position`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uuid_idx` (`uuid`),
  ADD KEY `IDX_462CE4F5727ACA70` (`parent_id`),
  ADD KEY `IDX_462CE4F5A76ED395` (`user_id`);
ALTER TABLE `user`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uuid_idx` (`uuid`);
ALTER TABLE `position`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1024;
ALTER TABLE `user`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1027;
ALTER TABLE `position`
  ADD CONSTRAINT `FK_462CE4F5727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `user` (`id`),
  ADD CONSTRAINT `FK_462CE4F5A76ED395` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`);
with recursive cte as (
  select `user_id` as `ancestor_id`
  , 0 as `degreesOfSeperation`
  , `user_id` 
  from `position`
  union all
  select c.`ancestor_id` 
  , c.`degreesOfSeperation` + 1
  , p.`user_id` 
  from `cte` as c
  inner join `position` as p
  on p.`parent_id` = c.`user_id`
)
select *
from cte c
order by c.`ancestor_id`
, c.`degreesOfSeperation`
, c.`user_id`
;


ancestor_id
degreesOfSeperation
user_id


3
0
3

3
1
4

3
1
5

3
1
9

3
1
14

3
2
6

3
2
7

3
2
8

3
2
9

3
2
11

3
2
12

3
2
13

3
2
20

3
3
10

3
3
11

3
3
12

3
3
13

3
3
16

3
3
17

3
3
18

3
3
19

3
3
20

3
4
14

3
4
15

3
4
16

3
4
18

3
4
18

3
4
19

4
0
4

4
1
6

4
1
7

4
2
10

4
2
11

4
3
14

4
3
15

4
3
16

4
3
18

5
0
5

5
1
8

5
1
9

5
1
11

5
2
12

5
2
13

5
2
16

5
2
17

5
2
20

5
3
18

5
3
19

6
0
6

6
1
10

6
2
14

6
2
15

6
2
18

7
0
7

7
1
11

7
2
16

8
0
8

8
1
17

9
0
9

9
0
9

9
1
12

9
1
12

9
1
13

9
1
13

9
1
20

9
1
20

9
2
18

9
2
18

9
2
19

9
2
19

10
0
10

10
1
14

10
1
15

10
1
18

11
0
11

11
0
11

11
1
16

11
1
16

12
0
12

12
1
18

13
0
13

13
1
19

14
0
14

14
0
14

15
0
15

16
0
16

17
0
17

18
0
18

18
0
18

19
0
19

20
0
20

ancestor_id	degreesOfSeperation	user_id
3	0	3
3	1	4
3	1	5
3	1	9
3	1	14
3	2	6
3	2	7
3	2	8
3	2	9
3	2	11
3	2	12
3	2	13
3	2	20
3	3	10
3	3	11
3	3	12
3	3	13
3	3	16
3	3	17
3	3	18
3	3	19
3	3	20
3	4	14
3	4	15
3	4	16
3	4	18
3	4	18
3	4	19
4	0	4
4	1	6
4	1	7
4	2	10
4	2	11
4	3	14
4	3	15
4	3	16
4	3	18
5	0	5
5	1	8
5	1	9
5	1	11
5	2	12
5	2	13
5	2	16
5	2	17
5	2	20
5	3	18
5	3	19
6	0	6
6	1	10
6	2	14
6	2	15
6	2	18
7	0	7
7	1	11
7	2	16
8	0	8
8	1	17
9	0	9
9	0	9
9	1	12
9	1	12
9	1	13
9	1	13
9	1	20
9	1	20
9	2	18
9	2	18
9	2	19
9	2	19
10	0	10
10	1	14
10	1	15
10	1	18
11	0	11
11	0	11
11	1	16
11	1	16
12	0	12
12	1	18
13	0	13
13	1	19
14	0	14
14	0	14
15	0	15
16	0	16
17	0	17
18	0	18
18	0	18
19	0	19
20	0	20

ancestor_id	degreesOfSeperation	user_id
3	0	3
3	1	4
3	1	5
3	1	9
3	1	14
3	2	6
3	2	7
3	2	8
3	2	9
3	2	11
3	2	12
3	2	13
3	2	20
3	3	10
3	3	11
3	3	12
3	3	13
3	3	16
3	3	17
3	3	18
3	3	19
3	3	20
3	4	14
3	4	15
3	4	16
3	4	18
3	4	18
3	4	19
4	0	4
4	1	6
4	1	7
4	2	10
4	2	11
4	3	14
4	3	15
4	3	16
4	3	18
5	0	5
5	1	8
5	1	9
5	1	11
5	2	12
5	2	13
5	2	16
5	2	17
5	2	20
5	3	18
5	3	19
6	0	6
6	1	10
6	2	14
6	2	15
6	2	18
7	0	7
7	1	11
7	2	16
8	0	8
8	1	17
9	0	9
9	0	9
9	1	12
9	1	12
9	1	13
9	1	13
9	1	20
9	1	20
9	2	18
9	2	18
9	2	19
9	2	19
10	0	10
10	1	14
10	1	15
10	1	18
11	0	11
11	0	11
11	1	16
11	1	16
12	0	12
12	1	18
13	0	13
13	1	19
14	0	14
14	0	14
15	0	15
16	0	16
17	0	17
18	0	18
18	0	18
19	0	19
20	0	20

ancestor_id	degreesOfSeperation	user_id
3	0	3
3	1	4
3	1	5
3	1	9
3	1	14
3	2	6
3	2	7
3	2	8
3	2	9
3	2	11
3	2	12
3	2	13
3	2	20
3	3	10
3	3	11
3	3	12
3	3	13
3	3	16
3	3	17
3	3	18
3	3	19
3	3	20
3	4	14
3	4	15
3	4	16
3	4	18
3	4	18
3	4	19
4	0	4
4	1	6
4	1	7
4	2	10
4	2	11
4	3	14
4	3	15
4	3	16
4	3	18
5	0	5
5	1	8
5	1	9
5	1	11
5	2	12
5	2	13
5	2	16
5	2	17
5	2	20
5	3	18
5	3	19
6	0	6
6	1	10
6	2	14
6	2	15
6	2	18
7	0	7
7	1	11
7	2	16
8	0	8
8	1	17
9	0	9
9	0	9
9	1	12
9	1	12
9	1	13
9	1	13
9	1	20
9	1	20
9	2	18
9	2	18
9	2	19
9	2	19
10	0	10
10	1	14
10	1	15
10	1	18
11	0	11
11	0	11
11	1	16
11	1	16
12	0	12
12	1	18
13	0	13
13	1	19
14	0	14
14	0	14
15	0	15
16	0	16
17	0	17
18	0	18
18	0	18
19	0	19
20	0	20