Page 1 of 1

Nested Sets

Posted: Fri Nov 07, 2003 6:13 am
by RayMatrix
Hi,

i have a problem to Display the Tree in the right Form

The Table:

Code: Select all

# Table structure for mitarbeiter
#----------------------------
drop table if exists mitarbeiter;
create table mitarbeiter (
   id int(11) not null auto_increment,
   name varchar(24) not null,
   umsatz varchar(24) not null,
   l int(11) not null default '0',
   r int(11) not null default '0',
   primary key (id))
   type=MyISAM;

#----------------------------
# Records for table mitarbeiter
#----------------------------


insert  into mitarbeiter values (1, 'user1', '1000', 1, 24) ;
insert  into mitarbeiter values (2, 'user2', '2000', 2, 9) ;
insert  into mitarbeiter values (3, 'user3', '3000', 3, 8) ;
insert  into mitarbeiter values (4, 'user4', '4000', 4, 5) ;
insert  into mitarbeiter values (5, 'user5', '5000', 6, 7) ;
insert  into mitarbeiter values (6, 'user6', '6000', 10, 17) ;
insert  into mitarbeiter values (7, 'user7', '7000', 11, 16) ;
insert  into mitarbeiter values (8, 'user8', '8000', 12, 13) ;
insert  into mitarbeiter values (9, 'user9', '9000', 14, 15) ;
insert  into mitarbeiter values (10, 'user10', '10000', 18, 23) ;
insert  into mitarbeiter values (11, 'user11', '11000', 19, 20) ;
insert  into mitarbeiter values (12, 'user12', '12000', 21, 22) ;
The 1st Query:

Code: Select all

$query = "SELECT pages1.id AS ID , 
      CONCAT( REPEAT('- ', COUNT(*) ), 
                      pages1.Name, 
                      '  (', 
                      pages1.l, 
                      '|', 
                      pages1.r, 
                      ')' ) AS Baum 

    FROM struktur AS pages1, 
        struktur AS pages2 

    WHERE pages1.l BETWEEN pages2.l AND pages2.r 
     
    GROUP BY pages1.l";
This shows the complete Tree and it looks like it should to.

And here comes my problem. I only like to view a part of the tree, the query works well but it do not show the 'tabs'.

The 2nd Query:

Code: Select all

$query = "SELECT pages1.ID AS ID , pages2.ID AS Betreuer, 
      CONCAT( REPEAT('- ', COUNT(*) ), 
                      pages1.Name, 
                      '  (', 
                      pages1.struktur_l, 
                      '|', 
                      pages1.struktur_r, 
                      ')' ) AS Baum 

    FROM struktur AS pages1, 
        struktur AS pages2 

    WHERE pages1.struktur_l BETWEEN pages2.struktur_l AND pages2.struktur_r 
     
    AND   pages2.ID = 2 
     
    GROUP BY pages1.struktur_l";
Anyone have a hint for me to solf this ??

Posted: Fri Nov 07, 2003 6:57 am
by Weirdan
Ok, in the first query WHERE clause selects the combination of pages1 with each of its parents, so when it's GROUPed the COUNT(*) returns the nesting level of pages1.
On the other hand in the second query you have fixed pages2 (pages2.ID = 2), WHERE select only one parent, so when it's GROUPed the COUNT(*) always returns `1`.

You need one more join:

Code: Select all

SELECT pages1.ID AS ID , pages2.ID AS Betreuer,      
             CONCAT( REPEAT('-', COUNT(*) ),
                      pages1.Name,
                      '  (',
                      pages1.l,
                      '|',
                      pages1.r,
                      ')' ) AS Baum
    FROM mitarbeiter AS pages1,
        mitarbeiter AS pages2,
        mitarbeiter AS pages3
    WHERE pages1.l BETWEEN pages2.l AND pages2.r
    AND   pages2.l BETWEEN pages3.l AND pages3.r
    AND   pages3.id=2
    GROUP BY pages1.l

Posted: Fri Nov 07, 2003 7:06 am
by RayMatrix
Boah man, you are genius !

Thank you very much !!