Pat Trainor wrote:
>
> This is 1/2 MySQL, and 1/2 Perl.. there isn't much left in life
> after that split..
>
> I am interested in what you all would say about how to approach
> this:
>
> given: latest mysql and perl and apache...
>
> There is a single table with 2 columns: id and parent. Each parent
> entry is the id of another row. One parent can have many children. The id
> is unique. There are children of children, etc..
>
> Now, here's what is desired: a way to query so that a tree is
> produced that shows all ids, and their relationship to their parent. I'll
> cgi it somehow, but the hard part _seems_ to be how to write a loop that
> will keep track of what _generation_ the child is, etc..
>
> Ideas?
>
Here's one, for what it's worth!
Add another column that will be 1 or 0 to show this record is a 2nd
generation:
mysql> SHOW FIELDS FROM tree;
+--------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+----------------+
| parent | int(11) | | | 0 | |
| child | int(11) | | PRI | 0 | auto_increment |
| SecGen | tinyint(4) | | | 0 | |
+--------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
Then you need to know the max number of generations in your table:
mysql> SELECT DISTINCT parent FROM tree WHERE SecGen > 0;
+--------+
| parent |
+--------+
| 0 |
| 1 |
| 2 |
| 3 |
+--------+
4 rows in set (0.00 sec)
Use the number of returned rows (in this case 4) to create the query:
mysql> SELECT t0.parent, t0.child, t1.child, t2.child FROM tree AS t0
-> LEFT JOIN tree AS t1 ON t1.parent = t0.child
-> LEFT JOIN tree AS t2 ON t2.parent = t1.child
-> WHERE t0.parent > 0 AND t0.SecGen ORDER BY t0.parent;
+--------+-------+-------+-------+
| parent | child | child | child |
+--------+-------+-------+-------+
| 1 | 25 | 34 | 36 |
| 1 | 25 | 34 | 37 |
| 1 | 25 | 35 | NULL |
| 1 | 26 | NULL | NULL |
| 1 | 27 | NULL | NULL |
| 1 | 28 | NULL | NULL |
| 2 | 29 | NULL | NULL |
| 2 | 30 | NULL | NULL |
| 2 | 31 | NULL | NULL |
| 3 | 32 | NULL | NULL |
| 3 | 33 | NULL | NULL |
+--------+-------+-------+-------+
A quick loop thru perl or C and Presto! :)
jim...
PS. If you want everyone whether or not they're part of a 'family':
mysql> SELECT t0.parent, t0.child, t1.child, t2.child FROM tree AS t0
-> LEFT JOIN tree AS t1 ON t1.parent = t0.child
-> LEFT JOIN tree AS t2 ON t2.parent = t1.child
-> WHERE (t0.parent > 0 AND t0.SecGen) OR
-> (t0.parent = 0 AND t0.SecGen = 0) ORDER BY t0.parent;
+--------+-------+-------+-------+
| parent | child | child | child |
+--------+-------+-------+-------+
| 0 | 38 | NULL | NULL |
| 1 | 25 | 34 | 36 |
| 1 | 25 | 34 | 37 |
| 1 | 25 | 35 | NULL |
| 1 | 26 | NULL | NULL |
| 1 | 27 | NULL | NULL |
| 1 | 28 | NULL | NULL |
| 2 | 29 | NULL | NULL |
| 2 | 30 | NULL | NULL |
| 2 | 31 | NULL | NULL |
| 3 | 32 | NULL | NULL |
| 3 | 33 | NULL | NULL |
+--------+-------+-------+-------+
