CODEWITHSUNDEEP
phpmysqldatabase
rjmcb
rjmcb

Reputation: 3745

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

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?

Upvotes: 5

Views: 5884

Answers (6)

Hardik Patel
Hardik Patel

Reputation: 838

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.

Upvotes: 1

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57650

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

Upvotes: 4

tereško
tereško

Reputation: 58444

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.

Upvotes: 1

sp00m
sp00m

Reputation: 48817

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 :)

Upvotes: 3

max
max

Reputation: 3716

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

Upvotes: 1

Broncko
Broncko

Reputation: 193

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.

Upvotes: 1

Related Questions