Search code examples
phpmysqlnested-sets

Showing users in tree structure?

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 the uuid of the inviter.
  • If a user was not invited, their parentUuid column is null
  • 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?

Solution

  • 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/