Member Avatar for ikrami.hall

count all left and right nodes

12.png 56.74 KB
  • 2 Contributors
  • 4 Replies
  • 84 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by ikrami.hall

Dani AI

Generated

Short answer for and follow-up to : decide how the tree is stored first. Two practical schemas and their solutions:

  • Adjacency list with a side flag (columns: id, parent_id, side where side = 'L' or 'R') or with explicit child pointers (left_id, right_id). Use a recursive CTE (MySQL 8+) to build a descendant closure and then count descendants of the left child and the right child separately.
WITH RECURSIVE descendants(ancestor, descendant) AS (
  SELECT id, id FROM nodes
  UNION ALL
  SELECT d.ancestor, n.id
  FROM descendants d
  JOIN nodes n ON n.parent_id = d.descendant
)
SELECT p.id AS parent_id,
  (SELECT COUNT(*) FROM descendants WHERE ancestor = l.id) AS left_count,
  (SELECT COUNT(*) FROM descendants WHERE ancestor = r.id) AS right_count
FROM nodes p
LEFT JOIN nodes l ON l.parent_id = p.id AND l.side = 'L'
LEFT JOIN nodes r ON r.parent_id = p.id AND r.side = 'R';
  • Nested set (columns lft,rgt): you can get subtree size in O(1) per node: subtree_nodes = (rgt - lft + 1) / 2. If you keep left/right child ids you can compute left/right counts by reading those child rows and applying the formula.

Notes and tips: recursive CTEs require MySQL 8.0+ (WITH RECURSIVE docs). For very large or frequently-updated trees, precompute counts or use nested sets for fast reads. Always index parent_id (and side if used). If running on MySQL <8, do the traversal in application code or with a stored procedure that iterates.

Recommended Answers

All 4 Replies

Member Avatar for pritaeas

What do you have so far?

Member Avatar for ikrami.hall

i have count for first level of child , but i need all child count for the parent

Member Avatar for pritaeas

What is the output you are expecting?

Member Avatar for ikrami.hall

every ID with all childs for example
(Parent) ikrami
(Left) karim
(Right) hady

so i need to get for ikrami in left column ( Karim and all childs left or right from karim ) and in column right ( hady and all childs left or right from hady )

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.