0

I'm looking for a query to create a table in MySQL with the degree of seperation between two users. I already found Degrees of Separation Query.But that will, if i understand correctly, result in a recommended friendlist with mutual friends. What i'm looking for is slightly different.

I have a table with "friends" between users (contains no duplicate relations like 1 to 2 & 2 to 1).

friends (id, initiator_user_id, friend_user_id, is_confirmed)

What i am trying to create is a table with all relations between friends, friends of friends and FoFoF. like this:

relation_degrees (id, first_user_id, second_user_id, relation_degree)

so the relation_degree column only contains the value 1 (friends), 2 (FoF) and 3 (FoFoF).

I was able to do it in Excel, but there my friends where stored in a matrix, which make calculations IMO a little bit easier. I hope somebody will be able to give me a hint to do the same in MySQL.

Thanks!!

edit: with the help from Fluffeh i found the following solution to my problem.

  1. i stored the relations in both directions( like 1-2 & 2-1, so without the confirmation column) in table called degree_one
  2. Then i used the query for degree one and two from fluffeh to make table with first & second degree relations. I added a WHERE user <> Friend statement to filter the relations (i guess this is one of the reasons why the query from fluffeh for the third degree relations isn't working correctly)
   `Create table degree_two
   select
   mb.user as User,
   mb.friend as Friend,
   min(mb.rel)  as relation_degree

from ( select 1 as rel, fr1.User, fr1.Friend from degree_one fr1

          union all

      select
          2 as rel,
           fr2.User,
           fr3.Friend
       from
           degree_one fr2
               left outer join degree_one fr3
                   on fr2.Friend=fr3.User


                ) mb

   Where user <> friend



 group by
      mb.User,
      mb.Friend
  1. Then i used this table to do almost the same query. the select statement is on the degree_two table but the outer join is still from the degree_one table.
   Create table degree_three
    select
        mb.user as User,
        mb.friend as Friend,
        min(mb.relation_degree)  as relation_degree
    from
        (
             select
                fr1.relation_degree,
                fr1.User,
                fr1.Friend
            from
                degree_two fr1


          union all

      select
            3 as rel,
            fr2.User,
            fr3.Friend
        from
            degree_two fr2
                left outer join degree_one fr3
                    on fr2.Friend=fr3.User


                ) mb

Where `user` <> `friend`

group by
    mb.User,
    mb.Friend

It is kind of a work around but it gives me the desired output. I'm still wondering why the query from fluffeh doesn't work correctly, because i really want a single query as solution. i will continue fooling around with the query... I hope that somebody can help me merging these query into one.

Community
  • 1
  • 1
Florian
  • 725
  • 6
  • 27

2 Answers2

1

You can do this with outer joins back to the table itself...

select
    mb.initiator_user_id as first_user_id,
    mb.friend_user_id as second_user_id,
    mb.rel as relation_degree
from
    (
        select
            1 as rel,
            fr1.initiator_user_id,
            fr1.friend_user_id
        from
            friends fr1

        union all

        select
            2 as rel,
            fr2.initiator_user_id,
            fr3.friend_user_id
        from
            friends fr2
                left outer join friends fr3
                    on fr2.friend_user_id=fr3.initiator_user_id
        // and again etc or in a code loop (not really done these much)
    ) mb

Basically, it seems you have the IDs able to be linked from friend1 to friend2, but the structure also allows for a query that allows friend1 to use friend2 to see who THEIR friends are - you can union these results with the degree easily enough.

Edit: Resolving issues based on comment:

select
    mb.initiator_user_id as first_user_id,
    mb.friend_user_id as second_user_id,
    min(mb.rel)  as relation_degree
from
    (
        select
            1 as rel,
            fr1.initiator_user_id,
            fr1.friend_user_id
        from
            friends fr1
        where is_confirmed = 1

        union all

        select
            2 as rel,
            fr2.initiator_user_id,
            fr3.friend_user_id
        from
            friends fr2
                left outer join friends fr3
                    on fr2.friend_user_id=fr3.initiator_user_id
                    and fr3.is_confirmed = 1
        // and again etc or in a code loop (not really done these much)
    ) mb
group by
    mb.initiator_user_id,
    mb.friend_user_id

Edit: Adding third layer of relationships based on comment:

select
    mb.initiator_user_id as first_user_id,
    mb.friend_user_id as second_user_id,
    min(mb.rel)  as relation_degree
from
    (
        select
            1 as rel,
            fr1.initiator_user_id,
            fr1.friend_user_id
        from
            friends fr1
        where is_confirmed = 1

        union all

        select
            2 as rel,
            fr2.initiator_user_id,
            fr3.friend_user_id
        from
            friends fr2
                left outer join friends fr3
                    on fr2.friend_user_id=fr3.initiator_user_id
                    and fr3.is_confirmed = 1
        union all
        select 
            3 as rel, 
            fr4.initiator_user_id, 
            fr5.friend_user_id 
        from 
            friends fr3 
                left outer join friends fr4 
                    on fr3.friend_user_id=fr4.initiator_user_id 
                    and fr4.is_confirmed = 1
                left outer join friends fr5
                    on fr4.friend_user_id=fr5.initiator_user_id 
                    and fr5.is_confirmed = 1
        // and again etc or in a code loop (not really done these much)
    ) mb
group by
    mb.initiator_user_id,
    mb.friend_user_id
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • thanks Fluffeh for your fast answer. When i try the query i get an error saying > #1066 - Not unique table/alias: 'fr1'. You have any idee? – Florian May 22 '14 at 08:59
  • Thanks again! i tried again and got the same error but on the fr3. #1066 - Not unique table/alias: 'fr3'. in the mean time: where is the mb. standing for? I'm not able to find any info about it. – Florian May 22 '14 at 09:16
  • Awesome it works! There are only 2 problems remaining: 1. The non Confirmed relations are also in the list. Can a [WHERE is_confirmed = 1] statement solve this? and where does it have to be placed... 2. Actually i only want the shortest path in the list. So if 2 users a first degree friends i dont want them to be also listed as second degree friends. Do you have any suggestions on this matter? For now I will study this query to understand what is actually happening..many Thanks! – Florian May 22 '14 at 09:55
  • I have tried to ad the code below to add 3 degree relations, needless to say it didn't work. any tips? ' Union all select 3 as rel, fr3.initiator_user_id, fr4.friend_user_id from friends fr3 left outer join friends fr4 on fr3.friend_user_id=fr4.initiator_user_id and fr4.is_confirmed = 1' – Florian May 22 '14 at 11:00
  • @Florian I added how I think the third layer would work. Want to give it a whirl? It is basically adding a third layer to the outer join performed on the initial table. – Fluffeh May 22 '14 at 11:08
  • This query is not working correctly, it only gives me one 3rd degree relation on my database, while i know there are supposed to be like 39 of them. Also i found that sometimes the initiator_id or friend_id are NULL and sometimes that users have relationships with themselves. any clue? :) – Florian May 22 '14 at 11:28
  • @Florian The code that I wrote will specify each particular level. I know that queries can be written that will loop around - but I have not written or needed these. Let me see what I can do by calling in friends. – Fluffeh May 22 '14 at 11:39
  • @JohnWoo Hey matey, any chance of a recursive query to pick out all the relationship levels in this dataset? – Fluffeh May 22 '14 at 11:40
  • @JohnWoo: He man, I really wish you could help me out with this one, you would help me big time!! – Florian May 26 '14 at 10:44
  • Thanks for your efforts and help! i'm still working on the problem, do you know where i can find more information about FoF and FoFoF querys in MySQL? – Florian May 26 '14 at 10:46
  • @Florian I found these links to recursive queries: http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql http://stackoverflow.com/questions/10646833/using-mysql-query-to-traverse-rows-to-make-a-recursive-tree http://mysqlserverteam.com/with-recursive-and-mysql/ – Fluffeh May 26 '14 at 10:55
0

This query gave me the desired solution result:

Create table degree_two
select
    mb.user as User,
    mb.friend as Friend,
    min(mb.rel)  as relation_degree
from
    (
           select
            1 as rel,
            fr1.User,
            fr1.Friend
        from
            degree_one fr1

            union all

        select
            2 as rel,
            fr2.User,
            fr3.Friend
        from
            degree_one fr2
                left outer join degree_one fr3
                    on fr2.Friend=fr3.User
                    where fr2.user <> fr3.friend

         union all       

         select 
            3 as rel, 
            fr4.user, 
            fr6.Friend
        from 
           (degree_one fr4 
                left outer join  degree_one fr5 
                    on fr4.friend=fr5.user
                    and fr4.user <> fr5.friend

                    left outer join  degree_one fr6
                    on fr5.friend = fr6.user
                   and fr5.user <> fr6.friend)
           where fr6.friend  IS NOT NULL           


                  ) mb

group by
    mb.User,
    mb.Friend

Thanks Fluffeh for your help!

Florian
  • 725
  • 6
  • 27