MySQL - getting the parent id of the top level in the hierarchy

Question

Here is 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 is to get parent_position_id for a specific position_id :

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

But how can I get the topmost parent of a specific position_id ? For example, the topmost parent_position_id of position_id 15 will 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?

Answer 1

Your database structure will not allow you to do this unless there are 15 or more joins. You are using the Adjacency list model. Try using the Nested Dial Model

Here is an example with php

Answer 2

Similar to the same problem: Recursive PHP function to display adjacency list

Using the same query with mysql may be different. Perhaps you can solve this problem with a stored procedure.

Answer 3

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

1. SET parent_position_id for auto increment

2. select position_id from the table order by position_id desc and place them in an array

3. crop table

4. insert the array into the table

Answer 4

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

I think not, look here for Hierarchical Queries in MySQL

Do I need to create a loop statement in PHP?

I think yes.

Answer 5

Try the following:

 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 was looking for such a solution :)

Answer 6

In this table structure, the best option you have is a loop in PHP terms.

If the table structure is something that you can freely change (in case the project is no longer living), you may need to look at a structure called Closure Tables . You can find a simple example of how they are used / installed in this article .

In any case, you can find much more on this subject in the SQL Antipatterns book.