2

I have h that type table

enter image description here

Now we want full binary tree And full Left and right chides count

Manik khanna
  • 48
  • 9
  • 1
    Can you share any code you have so far. Not everyone is going to provide a free coding service for you. – Nigel Ren Sep 12 '17 at 06:55
  • I have create a function like function count($parent_id, $leg){$select = "select count(user_id) as total, user_id From users_tbl where parent_id = $parent_id AND leg = $leg"; foreach($select as $key => $value){$count += $value['total']; count($value['user_id'],$leg)}} – Manik khanna Sep 12 '17 at 06:59

3 Answers3

2

Unfortunately, this is not efficiently possible using only SQL (the fact that each node can only have two children, left and right, does not help because the nodes can still be nested arbitrarily deep), you have to do it recursively using PHP:

function countChildren($parentId) {
    $children = (`SELECT user_id FROM table where parent_id = ?`, $parentId); // Pseudocode, use a prepared statement with your ORM or MySQL library/PDO
    $count = count($children);
    foreach($children as $userId) {
        $count += countChildren($userId);
    }
    return $count;
}

However, if you switch your table to use a nested set instead of a tree, querying children for information becomes much more efficient:

SELECT count(*) FROM table AS t JOIN table as parent_table ON (t.left > parent_table.left AND t.right < parent_table.right) WHERE parent_table.user_id = ?;
Raphael Schweikert
  • 18,244
  • 6
  • 55
  • 75
1

You will need to keep function in loop then run it , hope this helps you.

Thanks

arvindpundir
  • 136
  • 1
  • 12
  • I will not recommend this method. Try https://stackoverflow.com/questions/53868711/get-all-children-by-parent-id-and-where-clause-in-mysql so that the recursion is done in MYSQL itself which also optimizes the performance rather doing on client end. – Amanjot Kaur Jan 06 '20 at 09:55
0
SELECT  leg , count(user_id)as Count FROM table GROUP BY leg

This will show the count of left and right

Arunprasath
  • 40
  • 6