Current USER table:
Field Type id int(11) firstname varchar(64) lastname varchar(64) status varchar(5) permission smallint(5)
Current USER_RELATIONS table: (it has 2 FOREIGN KEYs - ancestor->USER.id - descendant->USER.id)
Field Type relationId int(11) ancestor int(11) descendant int(11) length int(11)
Current TRANSACTIONS table: (it has 1 FOREIGN KEY - chid->USER.id)
Field Type id int(11) chid int(11) date date amt varchar(16)
All the relations are setup correctly and are created when a user joins and enters a reference of another user that has refered him, thus creating a hierarchial tree.
Previously I was trying to get this setup correctly and had some great help from "Puggan Se", whom pointed me in the direction of Closure Tables.
Currently I can see an entire tree for a single original referer (ancestor) with all its refered users (descendant). We have also setup an auto leveling system that will level each user that have refered a pre-defined number of decendants.
We have now added a shop and would like to give each user a little something back for their references and inviting other users to join and shop. The "little something" is basically a percentage of the sale, based on their tree structure.
Explained:
1: A invited B, C & D -> B invited E,F -> C invited G -> D invited no-one 2: A moves to status=2(because he invited 3) -> B moves to status=1 (cause he invited 2)-> C & D remain on status=0 (because minimum required invites = 2) 3: Now when B, C & D purchases something from the shop, A should ge a little something back. Because A is status 2, he will get X % for all status=1 sales and Y % for all status=0 sales 4: In the event that B surpases A in status, A will NOT get a "little something" back. 5: status=0 levels do not get something back.
Question: I want someone to review the MySQL query and tell me if I am doing it correctly. I want to get the total transactions and total amount spend by all descendants in an ancestor s tree where the descendant status < ancestor status. Can someone please help? Currently I run it 4 times for each relations.length/status= since there are 4 status levels below the highest which is 4.
The Query:
select COUNT(*) as total, SUM(amt) as amount from transactions left join card_holders_relations as t1 on transactions.chid = t1.descendant left join card_holders as t2 on t2.id = t1.descendant where t1.ancestor = 3 AND t2.status = 0 AND t1.length = 4;
Now each time the t2.status increases to NOT equal the ancestor status and the t1.length decreases to 1, since length = 0 is the ancestor himself.
Are my assumptions and methods correct or is there an easier way of doing this?
