SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2003
    Location
    Mars
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Whom to follow ?

    I have a website that have same idea of Twitter's follow feature

    one table has the relationship between users named user_followers as follows:

    user_id follower_id
    1---------2
    1---------3
    1---------4
    2---------1
    2---------4
    2---------5
    2---------6
    3---------4
    3---------5
    3---------6


    Now can I retrieve a list of users to suggest for a specific user like "Who to follow" in Twitter

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bmbsa View Post
    Now can I retrieve a list of users to suggest for a specific user like "Who to follow" in Twitter
    sure you can, i can help you with the sql

    but first, please give instructions on how it's supposed to work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2003
    Location
    Mars
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sure you can, i can help you with the sql

    but first, please give instructions on how it's supposed to work
    It's supposed to suggest the most followed users (say top 10) by my followers

    for example if I am a user A, and my followers are B and C

    B is following D and E
    C is following D and F

    In this case, user D should be in the top of suggestions list for user A.

  4. #4
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,154
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)
    In other words, who are my followers also following?
    Or do you mean, who are the ones I'm following following?

    In any case this could grow exponentially in complexity, I'm curious to see r937's query.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT my_followers_following.user_id
      FROM user_followers AS my_followers
    INNER
      JOIN user_followers AS my_followers_following
        ON my_followers_following.follower_id = my_followers.follower_id
       AND my_followers_following.user_id <> my_followers.user_id
     WHERE my_followers.user_id = 1   
    GROUP
        BY my_followers_following.user_id
    ORDER
        BY COUNT(*)
    i tested it for you, on the data you posted, using "i am user 1" instead of "i am user A", and it returns 2 and 3 (1 occurrence each)
    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
  •