How Can I Efficiently Query Descendants or Ancestors in a MySQL Tree Structure Without Specifying Depth?

Tree Structure Querying in MySQL: Depth-Independent Traversal

Accessing tree structure data in MySQL often involves recursive or sequential queries. However, it is possible to retrieve all descendants or ancestors of a given row in a tree-structured table without explicitly specifying the depth level using a single query. This technique known as a Modified Preorder Tree Traversal.

Query Methodology

In a tree structure table with columns id, data, and parent_id, a modified preorder traversal query can be formulated as follows:

SELECT id, data, parent_id
FROM tree_table
WHERE id IN (
    SELECT id
    FROM tree_table
    WHERE ancestry LIKE '%given_id/%'
)

Here, given_id represents the ID of the row for which you want to retrieve descendants or ancestors.

Usage and Implementation

The query string 'ancestry LIKE '%given_id/%'' filters for rows that have the given ID as part of their ancestry path. This ensures that all descendants (along with the given row) are returned. To retrieve ancestors, simply replace '%given_id/%' with '%/given_id/%'.

For example, in PHP with MySQLi:

$stmt = $mysqli->prepare("SELECT id, data, parent_id FROM tree_table WHERE id IN (SELECT id FROM tree_table WHERE ancestry LIKE '%?/%')");

Additional Information

The provided PHP example from Sitepoint (mentioned in the answer) offers a comprehensive illustration of this technique. For further insights into handling tree structures in SQL, refer to Joe Celko's "Trees and Hierarchies in SQL for Smarties."

The above is the detailed content of How Can I Efficiently Query Descendants or Ancestors in a MySQL Tree Structure Without Specifying Depth?. For more information, please follow other related articles on the PHP Chinese website!