MySQL Forums
Forum List  »  Quality Assurance

Interesting problem with Nested Set and duplicate names in (a) child node
Posted by: Jonathan Huurman
Date: February 01, 2008 04:15AM

Hi all,

I've been implementing the Nested Set model as described in this article: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

It all works fine, I've created some stored procedures to easily add items by issuing just one call command, etc.


There's only one problem I can't solve, because it basically seems unlogical to me at all;

Here's table category:

+----+-----------+------+------+
| id | name | lft | rgt |
+----+-----------+------+------+
| 1 | Software | 1 | 18 |
| 2 | Hardware | 19 | 22 |
| 3 | Adobe | 2 | 3 |
| 4 | Apple | 4 | 5 |
| 5 | Enfocus | 6 | 7 |
| 6 | Extensis | 8 | 9 |
| 7 | Filemaker | 10 | 11 |
| 8 | Microsoft | 12 | 13 |
| 9 | Quark | 14 | 15 |
| 10 | Other | 16 | 17 |
| 11 | Apple | 20 | 21 |
+----+-----------+------+------+

To go short on this one:
Software comprises Adobe, Apple, Enfocus, Extensis, Filemaker, Microsoft and Other
Hardware comprises Apple

Notice that Apple appears twice.

Here's the query I use to get the entire tree:

SELECT node.name, node.id, parent.id AS parent_id, COUNT( parent.id ) AS depth, node.lft, node.rgt
FROM category AS node, category AS parent
WHERE node.lft
BETWEEN parent.lft
AND parent.rgt
AND node.rgt
BETWEEN parent.lft
AND parent.rgt
GROUP BY node.name
ORDER BY node.lft, node.rgt

Now here's the interesing, yet faulty result this query returns:

+-----------+----+-----------+-------+------+------+
| name | id | parent_id | depth | lft | rgt |
+-----------+----+-----------+-------+------+------+
| Software | 1 | 1 | 1 | 1 | 18 |
| Adobe | 3 | 1 | 2 | 2 | 3 |
| Apple | 4 | 1 | 4 | 4 | 5 | --> ???? Apple depth 4??
| Enfocus | 5 | 1 | 2 | 6 | 7 |
| Extensis | 6 | 1 | 2 | 8 | 9 |
| Filemaker | 7 | 1 | 2 | 10 | 11 |
| Microsoft | 8 | 1 | 2 | 12 | 13 |
| Quark | 9 | 1 | 2 | 14 | 15 |
| Overige | 10 | 1 | 2 | 16 | 17 |
| Hardware | 2 | 2 | 1 | 19 | 22 |
+-----------+----+-----------+-------+------+------+

Apple only appears once, having a depth of four! If you check the table read-out, you'll find Apple twice, with correct lft and rgt values (1 between Software, the other between Hardware.)

I've been breaking my head on this a couple of hours, but I just can't locate the error.

N.B.: Both the database setup and the query have been taken from the article and rebuilt a bit, but when I use the original query to get the total tree, Apple also shows up the wrong way.

I'm using MySQL 5.0.45-community-nt under Windows.

If someone has a clue or a definite answer, I'd be very happy to hear from it!!!

Options: ReplyQuote


Subject
Views
Written By
Posted
Interesting problem with Nested Set and duplicate names in (a) child node
4164
February 01, 2008 04:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.