SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Harsh one: How to code 3rd level friends network

    I am building a business networking site. Each user will have own network of contacts, like on LinkedIn. So there will be his direct contacts (1st level), contacts of his contacts (2nd level) and 3rd level contacts.

    I am not sure what would be the best way to get information about entire user network to 3rd degree level.

    I have tried many things:
    -I created for each friend connection a new row in mysql database (id | userid | friendid). Than did query SELECT * FROM network WHERE userid=$userid, than with PHP while loop and query for each friend to get all contacts of user friends and than again the same to get 3rd level.
    -all 1st level friends in 1 field as text (separate them with : and than use PHP explode). Than do similar same to get 2nd and 3rd level.
    -again for each 1st level friend connection a separated row but all 2nd and 3rd contacts into 1 field as text (id | userid | friendid | networkoffriend (separate 2nd level with ":" and 3rd level with ";" ).

    All options are far too slow. I have no idea how LinkedIn, Xing and other business social networking sites are doing this because if each user has 100 friends than there is 1mio users in 3rd level.

    Has anybody any idea?

    Thanks a lot!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm not even going to try to guess how large professional networking sites store their data

    for someone who is just starting out, however, the "(userid | friendid)" model works fine

    rather than doing a query, then looping over the results and doing a subsequent query for each row, you should be doing a self-join

    and yes, if each user has 100 friends, the network can get very large, so you will have to give some thought to exactly what kind of results are practical to show to a user -- and showing all 1,000,000 3rd-level contacts doesn't sound very practical
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, I will try also self-join. Is there any other way I should try?

    I mentioned 1,000,000 3rd-level contacts because this will be related also to search and I need to display a number of results. But I am not sure if it is even possible to realize this with basic solutions, if not I will use only 2nd level contacts.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,095
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Or you could show those 3rd level contacts who are friends with 2 of more 2nd level contacts.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm not even going to try to guess how large professional networking sites store their data

    for someone who is just starting out, however, the "(userid | friendid)" model works fine

    rather than doing a query, then looping over the results and doing a subsequent query for each row, you should be doing a self-join

    and yes, if each user has 100 friends, the network can get very large, so you will have to give some thought to exactly what kind of results are practical to show to a user -- and showing all 1,000,000 3rd-level contacts doesn't sound very practical
    I tried the below code and this took the longest time. Actually it didn't stop loading. Did I make anything wrong?

    mysql_query("SELECT friendid FROM friends WHERE userid IN ( SELECT friendid FROM friends WHERE userid=203)") or die(mysql_error());

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    did you declare any indexes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no, I am not sure what should be indexed.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, please do a SHOW CREATE TABLE for the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •