I have a database table called users
This table has two columns (that are important)
uuid
, and parentUuid
Here are the rules for the table:
- If a user invites another user, the invited users
parentUuid
column is equal to theuuid
of the inviter. - If a user was not invited, their
parentUuid
column isnull
- There can be infinite levels of users.
What I want to do is, create a function function counter($levels, $uuid){}
When counter is called (lets assume $levels = 3)
I want the function开发者_高级运维 to return an array that looks like
array(0 => 200, 1 => 600, 2 => 1800);
So the basic idea is I want it to count down, for $levels
levels how many users are in the tree under the user.
What is the best way to do this?
I'd extend your users table to include a "level" column, that indicates the depth in level from the root that the user is. That way, when a new user gets added, their level just gets set to the parent's level + 1.
I know this is a bit different of a solution than what you're asking for, but the traversal process for getting the data you want from a table that doesn't store user levels is tricky at best, and the execution time is potentially very long. This seems to be a good example of a situation where the best solution is a slight modification to your schema.
What you're looking at is hierarchical data stored in a tree using the adjacency list model. This is very difficult to scale. Try converting it into a nested set instead. What the difference is and how to structure your data is very well explained here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
SAP uses hierarchies almost everywhere this the the structure of the table:
Firstly you need to make sure that your list of entries has a unique identifier UnID even if the bit of information you need is not the UnID. The UnID can be numeric for ease of incrementation.
Then when you add items to your hierarchy table you should store them in this format:
NodeID
Note: 0 is always the root nodeID. Any other entry is the UnId of the new entry. It can never be null
ParentID
Note: this is the UnID of the parent that you want to attribute to the new entry. It can never be null
ChildId
Note: this can be null. It is updated only when this New entry gets to be a parent.
NextId
Note: this is the important one. It determines which child is next in the sequence of children below the parent. The last one in the sequence is always null
Level
Note: this ensures that a UnID cannot be the parent at more than one level.
You will need some program logic to iron out the attempts to create circular references mentioned in a comment somewhere here, but by implication this means that when a referral takes place you must update three records in your hierarchy table: The new entry, the parent and the last child of the parent (with the nextID) to ensure your pyramid is correct.
There are multiple ways of storing hierarchical data in a database. I suggest using Materialized Path. Failing that, Nested Sets and Adjacency Lists also work.
https://communities.bmc.com/communities/docs/DOC-9902
精彩评论