volos volos - 6 months ago 47
SQL Question

Get sum from nodes tree

I'm learn php.
I have this struct

company 1 - $10| all $50
-company 1.1 - $10| all $20
--company 1.1.1 - 10$| all $10
-company 1.2 - $20| all $20


each company might have several child-company, and might only one parent. Each company has money.
All companys have Allmoney - own money + money of all his child companys.

In MySQL this struct like this

id|parent_id|name|money|allmoney
1| 0| company 1| 10|###
2| 1| company 1.1|10 |###
3| 2| company 1.1.1|10 |###
4| 1| company 1.2|10 |###


so, How I calculate allmoney for each company in php? I now, that need using recursion, but I try and nothing can't happen.
SELECT, UPDATE and other command mysql - I know, Please help me with php.
I writing something like this:

function updatemoney($id)
{
$data = CS50::query("SELECT ...", $id);
$allmoney = 0;

if(count($data) > 0)
{
foreach($data as $row)
{

$allmoney += $row["cash"];
//somewhere this, maybe need ubdate my db
$allmoney += updatemoney($row["id"]);
}
}
else return 0;
}


Thank you very much

Answer

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.

Comments