[RESOLVED] Dynamic Query Possible?

rulian

Hello,

I'm trying to figure out a way that I can make a query select specific fields which are dependent on another parameter's results.

That sounds confusing, basically what I'm trying to do is avoid multiple queries for a group rights manage table.

The basic structure I'm working with is linking groups in a hierchy, so that parent groups can access their child groups hierchy,

So lets say we have these groups
A parent of - > [ B - C ]
B parent of [ D - F - G ]
C parent of [ E - F - I]
D parent of

so A could access pretty much everyone's, B could access D's, F's, G',s and their groups, such as D's I and H and so foruth

So basically I want to name a single group, and determine a groups right's to a 3rd degree, meaning
A - > C -> D - > I
0 - > 1 -> 2 - > 3
So A could not see I's groups.

now, I want to accomplish this is as little queries as possible, idealy one.

The groups are ordered in the DB as such

GROUP ID | GROUP LINK ID
A | B
A | C
A | D
B | E
B | G
B | I
C | G
C | F
G | V
G | X

i could do different queries, but that could get very numerous depending on how many groups are linked. so I wanted to determine the results of the whole query based on the return part of the same query.

Such as return A's group as 1d, 1d results group As 2D, 2D's results groups as 3D

is that possible in mysql, or how could I accomplish this?

Shrike

This is a common problem for which there are a few common solutions. The first and most simple is to use a recursive algorithm with each recursion causing a new query. As you've pointed out this can lead to alot of queries for a big hierarchy. The second, more complex solution is called modified preorder tree traversal. I can't do justice to it here so here's a useful PHP article, incidentally outlining both solutions.

cgraz

SELECT t1.groupID AS level1, t1.linkID AS level2, t2.linkID AS level3, t3.linkID AS level4 FROM linking_groups AS t1 
LEFT JOIN linking_groups AS t2
    ON t1.linkID = t2.groupID 
LEFT JOIN linking_groups AS t3 
    ON t2.linkID = t3.groupID 
WHERE t1.groupID = 'A' 
ORDER BY t1.linkID ASC;

returns

level1		level2		level3  	level4
A 		B 		E 		NULL
A 		B 		G 		V
A 		B 		G 		X
A 		B 		I 		NULL
A 		C 		G 		V
A 		C 		G 		X
A 		C 		F 		NULL
A 		D 		NULL 		NULL

So you could use PHP to loop through the db results and see that

A > B > E
A > B > G > V
A > B > G > X
A > B > I
A > C > G > V
A > C > G > X
A > C > F
A > D

I'm not sure if this is what you were looking for nor if this is the most efficient means, but hopefully it helps.

nemonoman

That is one ADMIRABLE query!

rulian

shrike: thank you for the recommendation, i'll read up on those solutions

rulian

cgraz: thank you for you help, that solution is working great for the test DB, I'm going to dysect it and wrap my head around it, but it's working great so far, I agree that is one admirable query!