0

I have a table:

CREATE TABLE `users` (
    `user_id` int(10) NOT NULL,
    `user_name` char(150) NOT NULL,
    `parent_id` int(10) NOT NULL,
    `leg` enum('l','r') NOT NULL,
    `status` enum('unactive','active','block') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

parent_id is the id where user join leg is the position of the user join l=left r=right.

Example:

enter image description here

Its two leg binary. I want to count all users in left side of top user. As in image 2nd level user use 1 level user id as parent ID.

Please help me

lukess
  • 964
  • 1
  • 14
  • 19
Sagar Saroha
  • 22
  • 9
  • I don't think this can be solved with a single query, but I think it's relatively easy with a recursive function. What have you tried so far? – rickdenhaan Jul 29 '17 at 22:32
  • Please use punctuation in your text (commas, periods, capitalization etc...). Your writing is difficult to understand (at least for me). – BeetleJuice Jul 29 '17 at 22:38

1 Answers1

0

At first, this looks like a problem to solve with recursion; but with a few changes to your database schema it could be done with a single query. As far as database queries go, it's not the most efficient way to do it, but it still beats recursion.

The basis for this answer comes from a ruby on rails gem

The idea is to use an ancestry field instead of parent_id. The ancestry field contains a reference to all of a record's parents. So for the following tree:

          1
    2           3
 4     5     6     7
8 9  10 11 12 13 14 15

The "ancestry" field would look like this:

Id  Ancestry
--------------
1   null or ''
2   '1L'
3   '1R'
4   '2L 1L'
5   '2R 1L'
6   '3L 1R'
7   '3R 1R'
8   '4L 2L 1L'
9   '4R 2L 1L'
10  '5L 2R 1L'
11  '5R 2R 1L'
12  '6L 3L 1R'
13  '6R 3L 1R'
14  '7L 3R 1R'
15  '7R 3R 1R'

So now, getting the count and/or all the children on the left of ID 1 is simple:

SELECT COUNT(id) AS child_count FROM users WHERE ancestry LIKE '%1L%'

SELECT * FROM users WHERE ancestry LIKE '%1L%'

To get the users on the left of ID 2:

SELECT * FROM users WHERE ancestry LIKE '%2L%'

Want the children on the right of ID 1?

SELECT * FROM users WHERE ancestry LIKE '%1R%'

Edit

Just realized that the fuzzy like will get some bad results once ancestry numbers get into the double digits. To solve this, use some delimiter in front of each ancestor, ie,

Id  Ancestry
--------------
1   null or ''
2   '_1L'
3   '_1R'
4   '_2L_1L'
5   '_2R_1L'
6   '_3L_1R'
7   '_3R_1R'
8   '_4L_2L_1L'
9   '_4R_2L_1L'
10  '_5L_2R_1L'
11  '_5R_2R_1L'
12  '_6L_3L_1R'
13  '_6R_3L_1R'
14  '_7L_3R_1R'
15  '_7R_3R_1R'

and then include that delimiter in the query:

SELECT COUNT(id) FROM users WHERE ancestry LIKE '%_1L%'

Tim Morton
  • 2,614
  • 1
  • 15
  • 23
  • Cleaner than this is the [Nested Set Model](https://en.m.wikipedia.org/wiki/Nested_set_model) where each node has a left and right ID and my descendants' IDs are always between mine ... which is where I initially thought was where you were going with this answer. Nested sets allow a simple integer range query to resolve any arbitrary depth below a given node. – Michael - sqlbot Jul 30 '17 at 03:51
  • Thanks i got big help with your table structure. my counting issue has been resolve using this completely. can you also tell me to display the tree using https://developers.google.com/chart/interactive/docs/gallery/orgchart with this structure – Sagar Saroha Jul 30 '17 at 05:48
  • I don't have any experience with the orgchart, but it looks like you would simply use php to iterate through the `data.addRows([` so that the javascript could then build the chart. Sorry I can't help you there. If my answer to this post helped you, please consider marking the answer accordingly. – Tim Morton Jul 31 '17 at 03:55