Finding Child and Child of Child and so on

Databases
1

Hi

Well, I have a situation, where I need to find all the Child, and Child of Child and so on, until no more records found.

for example, suppose,

There is a User A,
under A, there are a user B and C, under B, there is D,E and under C there is F,G and so on,

So what I want is that all the Child of A, Grand Child, Great Grand child etc etc until there is no more child. Well, another example could be the Family Tree, so in simple words I want all the descendants of A or any other user.

In the database, I have columns like this :

ID, ParentID, Name

So it will stats like this :

ID, ParentID, Name
1 0 A
2 1 B
3 1 C
4 2 D
.
.
.
.
.

Please guide me what should be the best approach to do this.

Thanks
Zeeshan

2

Read this: http://www.sitepoint.com/forums/showpost.php?p=4174394&postcount=3

3

well, thanks for your addition, but I dont think that will help. That has no similarity. In that case, there is a defined depth (4) but in my case, the depth is not defined. It may be 1 or it may be 10 or even more.

4

In that case, I guess you’ll have to do it with PHP (or whatever language you use).

5

oh yes it does :slight_smile:

so what is the maximum number of levels in your data?

and please don’t say that it could be infinite, because that’s just lazy

go take a look at your data, and count the levels

we’ll still be here when you get back

:slight_smile:

6

hi r973

Thanks for your reply, in fact, in my case its really infinite, its like an affiliate thing, and there are types of users.

For general user the commission will be given to Direct Referrals and 1 more lever. While for out special type users, the commission will be given based on the whole download no matter how deep is that. So I really do not know what the depth could be. :frowning:

7

okay, you’ll have to write some recursive application code

be prepared for really lousy performance

alternatively, you could try the nested set model

8

I needed the same thing, and didn’t find a good solution that was MySQL only.

There was this one:
http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

And some info can be found here:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

But both are way to complicated for what they need to be.

So, I came up with two ways to do this, both using recursion:
#1
$data = getTree(5, array() );

function getTree($id, $data) {
$data[$id][‘data’] = DB::selectRow('SELECT * FROM foo WHERE id = ’ . $id);
$rows = DB::select('SELECT * FROM foo WHERE parent_id = ’ . $id);
foreach ($rows as $row) {
$data[$id][‘chield’] = getTree($row[‘id’], $data);
}
return $data;
}

#2
Same as #1, but you load all the table in an array first.

9

no kidding

that’s the main reason i never implemented the nested set model (which is what both of those articles are about)

i hear that it’s pretty good on performance provided that you don’t update the data, which is hellish difficult

10

Yes, I agree !

Well, I just wrote a PHP function to do this. In fact, it is not good in terms of performance, but I make it better, like I used some query optimization etc etc, for example, all the affiliate ids that has no child are RULED OFF at first step. and so on.

Thanks a Million people. I agree that generally, these things have certain depth.