Re: Extracting Parent Model from Nested Sets Model

From: Maxx <xfearience_at_mail.ru>
Date: 20 Feb 2003 10:09:00 -0800
Message-ID: <dfd547a6.0302201008.5b172040_at_posting.google.com>


nuncanada_at_ig.com.br (Flavio Botelho) wrote in message news:<de0df942.0301220944.5458caf1_at_posting.google.com>...
> 1) parent-child approach :
> SELECT xar_cid,xar_name,xar_left
> FROM xar_categories
> WHERE xar_parent=$parent
>
> 2) celko approach (?) :
> SELECT
> children.xar_cid,children.xar_name,children.xar_left,children.xar_right,
> COUNT(parents.xar_cid) as indent
> FROM xar_categories AS parents, xar_categories AS children
> WHERE children.xar_left BETWEEN $left AND $right
> AND parents.xar_left BETWEEN $left - 1 AND $right
> AND children.xar_left BETWEEN parents.xar_left AND
> parents.xar_right
> AND children.xar_cid != parents.xar_cid
> GROUP BY children.xar_cid
> HAVING indent = 1

[skiped]

Having almost the same problem, I decided to add another field to the table to keep the node's level in the tree. Playing with this field you may easily get all direct children of a node:

SELECT * FROM categories
WHERE left BETWEEN $parent_left AND $parent_right   AND level = $parent_level + 1;

Those who did understand Celko's approach and work with MySQL+PHP, may have a look at a PHP library I made to handle Nested Sets tables in MySQL:

http://dev.e-taller.net/dbtree/

Although, it is good to add, update and delete records, I'd recommend to write your own SELECT queries to get data from a table instead of using the library function. Received on Thu Feb 20 2003 - 19:09:00 CET

Original text of this message