MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Alexander Newald Date:July 26 2004 11:02pm
Subject:Re: Re[2]: nested sets - Perhaps a solution!
View as plain text  
[how to store userpermissions in a tree in mysql]

Hi,

after a long time of try and error I have this as a result:

mysql> select * from node;
+---------+---------+---------+-----+-----+------+------+------+
| node_id | root_id | payload | lft | rgt | a    | c    | d    |
+---------+---------+---------+-----+-----+------+------+------+
|       1 |       0 | master  |   1 |   8 |    1 |    1 |    1 |
|       2 |       0 | user1   |   2 |   5 |    0 |    1 |    1 |
|       3 |       0 | user2   |   6 |   7 |    1 |    1 |    1 |
|       4 |       0 | user3   |   3 |   4 |    1 |    1 |    1 |
+---------+---------+---------+-----+-----+------+------+------+


mysql> SELECT node1.payload,COUNT(*) AS level FROM node AS node1, node as
node2 where node1.lft between node2.lft and node2.rgt group by node1.lft;
+---------+-------+
| payload | level |
+---------+-------+
| master  |     1 |
| user1   |     2 |
| user3   |     3 |
| user2   |     2 |
+---------+-------+


mysql> select min(node2.a),min(node2.c),min(node2.d) from node as node1,
node as node2 where (node1.lft between node2.lft and node2.rgt) and
node1.payload = "user3";
+--------------+--------------+--------------+
| min(node2.a) | min(node2.c) | min(node2.d) |
+--------------+--------------+--------------+
|            0 |            1 |            1 |
+--------------+--------------+--------------+

mysql> select min(node2.a),min(node2.c),min(node2.d) from node as node1,
node as node2 where (node1.lft between node2.lft and node2.rgt) and
node1.payload = "user1";
+--------------+--------------+--------------+
| min(node2.a) | min(node2.c) | min(node2.d) |
+--------------+--------------+--------------+
|            0 |            1 |            1 |
+--------------+--------------+--------------+

mysql> select min(node2.a),min(node2.c),min(node2.d) from node as node1,
node as node2 where (node1.lft between node2.lft and node2.rgt) and
node1.payload = "user2";
+--------------+--------------+--------------+
| min(node2.a) | min(node2.c) | min(node2.d) |
+--------------+--------------+--------------+
|            1 |            1 |            1 |
+--------------+--------------+--------------+


Can someone have a look at it? Is it right? If it is right - how much cpu
will this type of query cost if I have 10000 userids?

Thanks,

Alexander Newald

Thread
nested setsAlexander Newald26 Jul
  • Re: nested setsAman Raheja26 Jul
    • Re[2]: nested setsDebugasRu26 Jul
      • Re: Re[2]: nested setsAman Raheja26 Jul
  • Re: Re[2]: nested setsAlexander Newald26 Jul
  • Re: Re[2]: nested setsAlexander Newald26 Jul
  • Re: Re[2]: nested sets - Perhaps a solution!Alexander Newald27 Jul