Get sum from nodes tree

by

LTREE

You are almost on the right track. You almost stumbled upon the ‘LTREE’ system of storing hierachial data in a database. You just need to make a slight modidification. that’s all.

Your table might look like this:

CREATE TABLE Table1
    (`id` int, `parent_id` int, `name` varchar(13),
     `path` char(10),
     `money` int)
;

And your data might look like this.

(1, 0, 'company 1', '1', 10),
(2, 1, 'child 1', '1.1', 10),
(3, 2, 'child 2', '1.1.1', 10),
(4, 1, 'child 3', '1.2', 10,),
(4, 1, 'company 2', '2', 10),
(4, 1, 'child 2.1', '2.1', 10)

The path column helps to identify which company is a subsidiary of another company. Notice that you don’t actually need to have an allmoney column. This is dynamically generated.

And how do you find all the money that belongs to the first company?

select sum(money) from Table1 where path >= '1' and path < '2'

Notice that in the structure that we have created, child1 is the parent for child2. So how do we find the allmoney for child1?

select sum(money) from Table1 where path >= '1.1' and path < '1.2'

There is only one query and no recursion.

MPTT

Another popular approach for fetching hierarchical data is using Modified Pre Order Tree Traversal. There has been an excellent article on Sitepoint for many years which explains how this is done with lot’s of sample code.

More Related Contents:

  1. Echo menu tree with recursive function
  2. recursive function to get all the child categories
  3. How to update multiple MySQL table entries with different id?
  4. Can’t insert data into mysql database using PHP [closed]
  5. How to search by key=>value in a multidimensional array in PHP
  6. Warning: mysql_connect(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  7. How can I use PDO to fetch a results array in PHP?
  8. Is closing the mysql connection important?
  9. MySQL check if a table exists without throwing an exception
  10. Shortcomings of mysql_real_escape_string?
  11. Alternative to mysql_real_escape_string without connecting to DB
  12. HTML – Change\Update page contents without refreshing\reloading the page
  13. Calculate age based on date of birth
  14. How to write a stored procedure using phpmyadmin and how to use it through php?
  15. Doing calculations in MySQL vs PHP
  16. Geo-Search (Distance) in PHP/MySQL (Performance)
  17. PHP mysql search multiple tables using a keyword
  18. How do I count unique visitors to my site?
  19. php code to test pdo is available?
  20. PHP / MySQL build tree menu
  21. How to create Codeigniter language files from database?
  22. Laravel $q->where() between dates
  23. WordPress prepared statement with IN() condition
  24. PHP: Inserting Values from the Form into MySQL
  25. Using PHP to execute multiple MYSQL Queries
  26. Select MYSQL rows but rows into columns and column into rows
  27. How to run multiple insert query in SQL using PHP in one go?
  28. Auto Increment skipping numbers?
  29. PDO bindParam into one statement?
  30. How to make mysqli connect function?

Leave a Comment