MySQL Query for Closure Table

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?

链接地址: http://www.djcxy.com/p/93944.html

上一篇: 修改后的关闭表用于分层数据的风险和好处

下一篇: MySQL查询关闭表