0

I have a table called Clients. And at this table, I have an idFather to know who added this row to the table.

Sample:

id | user    | idFather | createdAt
---+---------+----------+-----------
01 | raphael | null     | 01-06-2016
02 | jose    | 01       | 03-06-2016
03 | ryan    | 01       | 02-06-2016
04 | alyssa  | 03       | 04-06-2016
05 | laysa   | 04       | 04-06-2016
06 | karen   | 02       | 04-06-2016
07 | roger   | 03       | 04-06-2016

Now i need know how much peoples an user invited indirectly. Sample:

The user (01 - Raphael ) invited the users ( 02 and 03 ) so:
    Raphael invited 2 directly
    And user (02 - Jose) invited no one
    But user (03 - Ryan) invited (04 and 07) total 2
    And user (04 was invited by 03) invited also the user (05) total 1

So Raphael invited 2 directly ( this i did not know )
And Raphael invited 3 indirectly ( this i need know )

I i'm trying look 7 levels under the user... i'll try explain with this tree:

Sample tree

EDIT: If the users from lv1 was hard to kick out from Couting... I can do this math on my system... i just need do recursively by 7 lvs... and count just lv1 and get the difference.

I'm looking to get a query result like this:

user    | indirectInviteds
--------+-----------------
raphael | 4

I'll search just 1 user per time... i`ll not try select ALL clients and look directly.. it will explode...!!!

Thanks for the help guys!

2 Answers 2

1

As your data is a tree structure, consider using the Nested Set data model.

A good write-up can be found at Mike Hillyer's website, here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

complete with lots of SQL to manage the tree data.

2
  • Can you give an sample? I did not understand how exactly to do it... Jul 12, 2016 at 2:59
  • I recently used Mike Hillyer's article to store tree data in a database using the nested set model. I wouldn't be able to add much that isn't in the article. It may be worth creating a table with the same columns and data that he uses and work through his SQL examples. That's what I did. I also drew a tree on a piece of paper and went through how to number the left and right values for each node. FWIW, It took me a little while before I understood it, so put a couple of hours aside to have a play. I think Mike's article is well written and a good introduction to the topic. Regards, Jul 12, 2016 at 7:42
0

MySQL has no simple way to do it.

Assuming you have no "loop" in the "tree", ... Write a stored routine to iterate until there are no more "children". Or, do the equivalent in client code, not SQL.

1
  • Can you give an sample code to iterate? it will not have loop inside.... Jul 12, 2016 at 2:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.