Threaded Discussion Complex Query Help

[LiveTronix]

Okay --

I am trying to limit the number of queries I hit the database with -- so rather than running a PHP loop I want to write on more complex SQL.

Thread table
-> ThreadID
-> ParentThreadID (can be unlimited levels deep)
-> more rows that don't matter

GroupPermission
-> GroupPermissionID
-> ThreadID
-> IntranetGroupID
-> Level (0 - No Accress, 1 - Read, 2 - Write, 3 - Write & Manage)

IntranetGroup
-> IntranetGroupID
-> GroupName

So permissions are inherit. So say there is thread structure like this.

Thread Records
ID - Name - ParentThreadID
1 - Home - 0
2 - Sub of "Home" - 1
3 - Sub of "Sub of Home" - 2

GroupPermissionRecords
ID - ThreadID - IntranetGroupID - Level
1 - 1 - 1 - 3

IntranetGroup
ID - GroupName
1 - Example

So permissions are inherit so any user in Example group should have "Write & Manage" permission to Home, Sub of Home, and Sub of Sub of Home.

I want to do 1 single sql query that shows any user who can see Sub of Sub of Home. Since it's not necessarly registered to a particular thread ID it's hard to do this with one query.

I do this twice -- once for specific users and once for groups so if I save a lot of queries for Groups, I will save them for Users too.

Is this possible? Any help would be appreciated.

Thanks in advance.

[LiveTronix]

Still looking for some help. If anyone needs more of an explanation let me know.

[stereofrog]

This may be useful

http://www.sitepoint.com/article/hie...-data-database

[LiveTronix]

Yes, that article was very useful as I was struggeling through getting that working...

Got to be an SQL solution to the above problem?

[stereofrog]

Basically, you're trying to find all of parents of given node. With adjacency model you've choosen it's impossible to write one single query for this, you'll need loops / recursion. Nested sets or paths model would be the better choice.

[r937]

Got to be an SQL solution to the above problem?

there might indeed be

my problem, however, is that i can't understand your problem

"I want to do 1 single sql query that shows any user who can see Sub of Sub of Home"

"sub of sub of home" has Thread id=3, so that part's easy, but what's a user? i don't see a user table