MySQL - Getting the top-level parent id in a hierarchy

Question

Here's my table structure...

TABLE : position_hierarchy_level

id parent_position_id position_id
 1 1                  2
 2 2                  3
 3 3                  4
 4 4                  5
 5 5                  6
 6 6                  7
 7 7                  8
 8 8                  9
 9 9                  10
 10 10                11
 11 11                12
 12 12                13
 13 13                14
 14 14                15

My query for getting the parent_position_id of a certain position_id is:

select `parent_position_id` from `position_hierarchy_level` where position_id= 15;

But how can I get the top-most parent of a certain position_id? For example, the top-most parent_position_id of position_id 15 would be 1.

Is there a convenient way to get this value using a single query? Or do I need to create a loop in PHP?

I don't see any row with `id = 15` – hjpotter92 Apr 17 '12 at 07:39 — hjpotter92, Apr 17 '12 at 07:39

Shiplu Mokaddim · Accepted Answer · 2012-04-17T08:28:56.113

4

Your database structure wouldnt let you do it unless there are 15 or more joins. You are using Adjacency list model. Try using The nested set model

Here is an example with php

edited Apr 17 '12 at 08:28

answered Apr 17 '12 at 07:59

Shiplu Mokaddim

56,364
17
141
187

score 3 · Answer 2 · edited May 23 '17 at 11:52

Try this:

DELIMITER $$

CREATE FUNCTION getTopParentPositionId(positionId INT) RETURNS INT DETERMINISTIC 
BEGIN
    DECLARE x INT;
    DECLARE y INT;
    SET x = positionId;
    sloop:LOOP
        SET y = NULL;
        SELECT parent_position_id INTO y FROM position_hierarchy_level WHERE position_id = x;
        IF y IS NULL THEN
            LEAVE sloop;
        END IF;  
        SET x = y;
        ITERATE sloop;
    END LOOP;
    RETURN x;
END $$

DELIMITER ;

Then:

SELECT getTopParentPositionId( 5 );

Obviously, you are not the only one who looked into thoose kind of solution :)

its working great, a very good mysql function – Murulimadhav Apr 21 '22 at 10:50 — Murulimadhav, Apr 21 '22 at 10:50

score 1 · Answer 3 · edited May 23 '17 at 11:59

1

Looks like the same problem: Recursive PHP function for adjacency-list display

Using a single query with mysql could be kind of different. Maybe you could solve this with a stored procedure.

edited May 23 '17 at 11:59

Community

1
1

answered Apr 17 '12 at 07:41

Broncko

193
1
6

I just need the top most parent – rjmcb Apr 17 '12 at 07:43
select id, parent_position_id, position_id from position_hierarchy_level where parent_position_id=min(parent_position_id) - or didnt i understand the problem? – Broncko Apr 17 '12 at 07:48
better: select id, position_id from position_hierarchy_level order by parent_position_id limit 1 – Broncko Apr 17 '12 at 07:51
i want to get the **parent_position_id** using a certain **position_id** it should reiterate in each record. – rjmcb Apr 17 '12 at 07:51
@Broncko, he need a tree data to get the top-most parent id. – ariefbayu Apr 17 '12 at 07:51
Then you should think about using a nested set. It definitely works better with deep hierarchical structures. http://de.wikipedia.org/wiki/Nested_Sets – Broncko Apr 17 '12 at 07:55

score 1 · Answer 4 · answered Apr 17 '12 at 07:52

if i understand correctly and you want to highest position_id to have 1 for parent_position_id and so on ...

1 . SET parent_position_id to auto increment

2 . select position_id from table order by position_id desc and put them in a array

3 . truncate table

4 . insert array to table

score 1 · Answer 5 · edited Apr 18 '12 at 05:08

1

Is there a convenient way to get it using a single query?

I think NO, take look here for Hierarchical queries in MySQL

do I need to create a loop statement in PHP?

I think YES.

edited Apr 18 '12 at 05:08

Shiplu Mokaddim

56,364
17
141
187

answered Apr 17 '12 at 07:58

Hardik Patel

838
5
12

score 1 · Answer 6 · answered Apr 17 '12 at 08:03

With this table structure the best option you have is a loop in php end of things.

If the table structure is something you can freely change (in case if project isn't live already), you might want to look into structure called Closure Tables. You can find a simple example of how the are used/setup in this article.

In any case, you should be able to find a lot more on the subject in the SQL Antipatterns book.

MySQL - Getting the top-level parent id in a hierarchy

6 Answers6

Linked