Recursive calls to database in perl

Question

I know there's an easy way of doing this, but my recursion abilities are out of practice. Given a database table that has three fields:

id
label
child_id

I should be able to put together a recursive function that will give output like this:

child (input of program)
  parent1
  parent2
    grandparent1
      great-grandparent1
    grandparent2
    grandparent3
  parent3
    grandparent4
    grandparent5

I know it should be easy, but I can't get my mind to go through the mental gymnastics to make it work. Also, is this a good thing to do? Seems like I might end up leaving open quite a few database connections.

I think this is the part making it difficult for me. I'm starting with a child_id, and working my way up. And a child can have many parents. So, the output would be the child id at the 'root' of the tree and then it's parents and grandparents for each branch. The more I think about it, it's just the traditional 'one parent, many grandparents' formula, except for semantics. I could just be over thinking it.

The table would look something like this:

table parents

id    child_id    label
 1     NULL       child
 2     1          parent1
 3     1          parent2
 4     1          parent3
 5     3          grandparent1
 6     3          grandparent2
 7     3          grandparent3
 8     5          great-grandparent1
 9     4          grandparent4
10     4          grandparent5

You know, I left out an important part (and, I think, the part that is making it confusing for me). I'm starting with a child_id, and working my way up. And a child can have many parents. So, the output would be the child id at the 'root' of the tree and then it's parents and grandparents for each branch. — coding_hero, Jun 25 '10 at 18:18
How large is your table? It might be a lot easier to load this into a data structure and do it in memory vs calling the database. Is running out of memory an issue here? — Mike, Jun 25 '10 at 18:22
imo, you are usually better off using a table structure with ID, Label, ParentID (the root node/record should have a null/zero parentID). The recursion function should accept 1 argument: ParentID. Loop through the result set, passing the ID to your Recursion Fcn. Logically, you won't recurse if there are no child records (ie, no records with this parent ID). Of course, it is WAY more efficient if your query does a group-by to count the number of child nodes and doesn't even recurse if the count is zero. — tgolisch, Jun 25 '10 at 18:27
Yah, this is actually querying the backend of a big application. I didn't come up with this table structure. So, it's non-changable factor. — coding_hero, Jun 25 '10 at 18:43
I don't think memory is a factor here. We're probably looking at a dozen or two entries here, max. — coding_hero, Jun 25 '10 at 18:44
Is label2 above a child or parent of label1? Does label1 have the child_id for label2, or is it the other way round? I initially thought label2 would be a child of label1, but your "a child has many parents" makes me confused and rethink that. A little example data goes a long way. — runrig, Jun 25 '10 at 19:14
@runrig - Yah, I admit I messed up the sample data. Think of it as the child at the root, and then the parents. It's a traditional structure, save for semantics. — coding_hero, Jun 25 '10 at 19:20

score 3 · Accepted Answer · answered Jun 25 '10 at 18:58

3

You could try this way

sub getChildren {
  my $id = shift;
  my $depth = shift;
  my $sql = qq/SELECT id,label,child_id FROM table WHERE id=?/;
  my $sth = $db->prepare($sql);
  my $sth->execute($id);
  while(my ($id,$label,$child_id)=$sth->fetchrow_array) {
    print " "x$depth,$label;
    getChildren($child_id,$depth++);
 }
}
getChildren($id);

answered Jun 25 '10 at 18:58

Toto

89,455
62
89
125

Awesome. That's about what I was trying to do, but I was trying to use a global $depth for some reason. If you read my note above, I'm actually starting with the children, but I think the same concept will work. – coding_hero Jun 25 '10 at 19:17
You only need to prepare the statement once. Or use prepare_cached(). – runrig Jun 26 '10 at 15:18
The $depth counter keeps counting, even when it should reset, for some reason. I think a prefix is more appropriate here (++$depth), but it still has the same issue either way. – coding_hero Jun 28 '10 at 22:02
Never mind, figured it out. The $depth++ incrementor needs to be located outside of the while loop. – coding_hero Jun 28 '10 at 22:36

score 0 · Answer 2 · answered Jun 27 '10 at 15:00

I actually explained a quite similar problem in my blog, Implementing a depth first search in a PostgreSQL stored procedure, and my way of solving this using perl.

If your database doesn't support stored procedures you can do the same thing client-side, but you need to fetch the entire table first and do it in memory.

You could of course do it recursively and fetch each entry as you go along, but it will not scale because of the SQL statement overhead (except maybe on SQLite). If performance is not a problem this must be by far the easiest solution.

Recursive calls to database in perl

2 Answers2