searching the tree

Is there any easy way to retrieve - by SELECT and LEFT JOIN statements - all (not only immediate) branches starting from the chosen node?

Sincerely,

Pawel

What does your table structure look like?

just one table with node inf. like:

id; parent_id; value; depth(how far from root)

Now - each LEFT JOIN ON will relate one level of the tree.

I'm not a db specialist so maybe my question is totally out of blue but here it is:

Would it be possible using SQL statements to retrieve all nodes which are downstream from given one?

Sincerely,

Pawel

It is possible with two disadvantages:
1. using one table multiple times within a query is very slow
2. the number of branches is always limited to your query. the more branches you want to have max defines the difficulty of your query and makes it slower.

It is something like

SELECT l1.id, l2.id, l3.id
FROM table l1
LEFT JOIN table l2 on l2.parent = l1.id
LEFT JOIN table l3 on l3.parent = l2.id
WHERE l1.parent=$given

Expand this as you like. Maybe with something like CONCAT(l1.id, l2.id, l3.id) as id to get only one field ... if this would work.

I would greatly advice you to make a function in you script that is called for every branch. A recursive function is the ideal solution here.
In my experience this is easier, more expandable and faster.

But if anyone has another conclusion, I'm (very) happy to read it ;-)

I was planning to get over this by introducing two fields:
1) depth - INT describing distance from the root
2) lineage - STRING which child is taking from parent and concat it with its own name.
this is idea from one of tutorials but I was hopping that there is some easier was which do not suffer from n+1 selection problem.

Thanks for your help,

Sincerely,

Pawel

Indeed, if you have a field in your database containing all IDs of its parents (like "4;12;16;45") you can indeed simplify the query.

Then it is something like:

SELECT id FROM table WHERE parents like '%$given_parent%' ORDER BY parents

But if this is better/quicker/more expandable then the script solution is all a matter of tree-complexity. When you have a site menu structure a script is better, but when you have a database with all people from a country and their relationships (parent/child e.g.) then the linked-parent-field will probably work better