Unanswered: Selecting a value based on a value based on a value ... [etc]
What I'm trying to make is a permission system for a website.
What I am trying to do, and hope to be able to do with a view of some sort, is to create a virtual column or table (view, or anything else that might help me), that gives me a row of values based on the initial value that I put in the select statement.
CREATE TABLE mgap_ranks(id INT(5) NOT NULL AUTO_INCREMENT, name VARCHAR(265) NOT NULL, inherit VARCHAR(265), PRIMARY KEY (id), UNIQUE (name))
INSERT INTO mgap_ranks(name, inherit) VALUES('Guest', null), ('Admin', 'Guest'), ('Admin', 'Guest')
The following statement:
select id from mgap_ranks where name = 'Admin' or inherit = 'Admin'
Returns the values 1 and 2 (1 being Guest, 2 being Admin)
Second example, and the question:
TRUNCATE TABLE mgap_ranks
INSERT INTO mgap_ranks(name, inherit) VALUES('r1', null), ('r2', 'r1'), ('r3', 'r2'), ('r4', 'r3'), ('r5', 'r4'), ('r6', 'r3'), ('r7', 'r6')
What I want, is a statement or view or something, that returns the ID of the values that fall under the inheritance of the rank provided.
If the value I provide is 'r2', I want the ID's of 'r2' and 'r1'
If the value I provide is 'r5', I want the ID's of 'r5', 'r4', 'r3', 'r2' and 'r1'
If the value I provide is 'r7', I want the ID's of 'r7', 'r6', 'r3', 'r2' and 'r1'
The me acting sad part:
Could anyone please help me with this? It is an issue I have spend some time on already, and one that I would like to solve soon before I have to make loops in my programming code. If the database can do it, it looks a lot better!
Thanks in front
Thank you already, even if you just tried it and didn't came up with a solution!
The idea is to have it dynamically deternime it's own amount of levels.
I've looked through the nested set model, but from what I understand of it, the use is not what I'm looking for here, since I only want a single parent, and a dynamic tree. Reorganizing the tree would be too much of an effort.
Since the tree is dynamic, I can't say how many levels the structure would contain, so I cannot use a fixed number of self-joins.