The Database Forum

**Synchronize** · 04-05-2006, 01:45 PM

I am trying to write a hierarchy systme to use for a database at the company I work for. I am going by this guide:
http://dev.mysql.com/tech-resources/...ical-data.html

Right now I have set the lft and rgt columns for about a little over 600 people on our database, the numbers seem to be fine. The only problem is when I try to make a downline report for some reason it doesn't seem to work.

There is 2 problems with it, first it skips the first generation, and secondly the generation (depth) numbers don't always show up right.

The query i am using is as follows:

Code:

SELECT node.*, (COUNT(parent.name) - (sub_tree.depth + 1)) AS gen
FROM ' . $table . ' AS node,
    ' . $table . ' AS parent,
    ' . $table . ' AS sub_parent,
    (
        SELECT node.id, (COUNT(parent.name) - 1) AS depth
        FROM ' . $table . ' AS node,
        ' . $table . ' AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.id = ' . $parent . '
        GROUP BY node.name
        ORDER BY node.lft
    )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.id = sub_tree.id
GROUP BY node.name
ORDER BY node.lft;

You can see a sample of what it prints out here:
https://dev.mywfm.com/tree.php

The fields are gen, last, first, code to id this person, L: left value, R: right value

A dump of everyone by generation (not using this method) can be found here:
https://dev.mywfm.com/dl.php
This one was generated using the parent method that was incorporated at first but we are trying to move to a more efficient method.

The table looks as follows:

Table: associate
Fields: id, code, name, last, <other personal fields>, sponsor_code, lft, rgt
id is an auto_increment id
code is a code given by the company to each associate
name and last are first and last names
sponsor_code is how the old system was running, sponsor_code basically was the code of the parent for this associate
lft and rgt are the columns for the hierarchy system that we are trying to incorporate.

Does anyone have any idea/suggestions or knows how to fix the problem? I would really appreciate some help as I've been looking at this query for hours trying to figure out why it is doing that.

Thanks a bunch!

**0beron** · 04-08-2006, 12:49 PM

Ouch... that looks complicated...

I've not taken much of a look at your query above, but have you made sure the database you are querying is in a consistent state, ie the lft and rgt values are all correct? Your select query could be fine but there may be a problem with your inserts and updates that make the generation numbers come out wrong.