Referred friends points query

Question

I'm trying to figure out what is the best way to achieve this, I really appreciate any input.

Part of my MYSQL Table:

ID , Username , Invited_by
1  , A        , 
2  , B        , 1
3  , C        , 2
4  , D        , 2
5  , E        , 4
6  , F        , 5

So i want to figure out who invited the most users, the trick part is that if A invited B and B invited C then i will count That A invited 2, what i'm trying to achieve is this.

ID , Username , Invited
1  , A        , 5
2  , B        , 4
3  , C        , 0
4  , D        , 2
5  , E        , 1
6  , F        , 0

Explanation

F invited no one, same for C
E invited F so he get 1 point
D invited E so it means after E registered he invited F so D get 2 points
B invited C and D, D then invited 2 people so B get 4 points
A invited B which he invited 4 so A get 5 people

I know it's complex, thats why i'm trying to figure out the optimal solution for it.

Thanks,

UPDATE

So after i tried different approaches, i believe the best approach that i came up with is the following:

Adding 1 field to the users table as 'total_invites' for example
building a query that will calculate the number for invitations for each user, starting from the newest users till old users since the new users are likely didn't have the chance to invite anyone yet.
run the query on the database "cronjob or on limited rows each time"
once i reach to a balanced table the logic will change instead of calculating the results once i need the report i will increase the counter for each account and related accounts whenever a new referred user sign in.

NOTES

Even building a query for a single user seems complicated and i think i have to do n queries until i reach the bottom of the invitation tree for that user
i guess once the table reach a balance state the logic will become much easier.

Please if you have any thoughts or references that might help i will be very thankful.

The only solution is to run several queries (depending on the max 'friend depth') via stored proc or in your app. — Vatev, Jul 29 '13 at 11:01
if F invites G, E gets also a point ? and G invites H, E gets another point? — Zelldon, Jul 29 '13 at 11:05
This is a problem about recursion in MySQL. As such, it's something of a FAQ. — Strawberry, Jul 29 '13 at 11:42
@Vatev thanks for your answer, any idea what is the best approach to do this? I updated my question with some thoughts let me know what do you think — trrrrrrm, Jul 29 '13 at 15:19

score 2 · Accepted Answer · edited May 23 '17 at 12:30

I dont know if this what you looking for but it will be little long with many left joins.

 select t1.ID , t1.Username , count(t2.ID) + count(t3.ID) +  count(t4.ID)  as Invited 
 from table1 t1
 left join table1 t2 On t1.ID = t2.Invited_by
 left join table1 t3 On t2.ID = t3.Invited_by
 left join table1 t4 On t3.ID = t4.Invited_by
 group by t1.ID

DEMO HERE

Output:

 ID     USERNAME    INVITED
  1       A            5
  2       B            4
  3       C            0
  4       D            2
  5       E            1
  6       F            0

if you concerned with deeply friends list then check this function procedure to loop throw them . get a recursive parent list

EDIT2:

an good aproche for what im thinking , its up to you of course if you wanna do it.

is instead of inserting the Id of previous invited by person you can Concatenate all previous Invited by persons. Like that

    (1, 'A', NULL),
    (2, 'B', 'A'),
    (3, 'C', 'A,B'),
    (4, 'D', 'A,B'),
    (5, 'E', 'A,B,D'),
    (6, 'F', 'A,B,D,E')

everytime you insert an invited person do it with concat previous values with the new invited by person . the you have already looped them. and you dont need to use many queries , just one.

take a look at this DEMO

if there is another level of invitation (say user F invites G), then you would need to add another `JOIN` and another `count`? — Carlos Campderrós, Jul 29 '13 at 11:56
@echo_Me Thank you for your answer i guess it will help me, may you please check my update and let me know your thoughts. — trrrrrrm, Jul 29 '13 at 15:21
@CarlosCampderrós true, thanks but i guess if i followed the updated approach this might help — trrrrrrm, Jul 29 '13 at 15:21
@ra_htial if you want see my thinking so its just my opinion , of course its up to you , take a look at the EDIT2 . hope it helps you. — echo_Me, Jul 29 '13 at 16:36

Referred friends points query

1 Answers1