Results 1 to 2 of 2

Thread: Friend Query

  1. #1
    Join Date
    Feb 2011
    Waterloo, Ontario, Canada

    Friend Query

    Let's say you're on facebook.com/<your friend's tag here>/friends

    Buttons pop up that allow you to add each person as a friend, or tell you that you're already friends with certain people. How would that query work? I can understand how it would display the list of friends, but how would it also know whether you're friends with the person or not?

    If I have personId 10, and I'm looking at personId 11's friend list, how would the query look that would get me whether or not I'm friends with each person in the friends list?

  2. #2
    Join Date
    Jul 2013
    Lets assume that 'friends' table has two columns: p1 and p2, both of same data type (unsigned integer) and both represent user ID. If we have a record in this table which contains p1 = 10 and p2 = 11 we can say that users with IDs 10 and 11 are friends. Also, when inserting "10,11" row we should automatically insert "11,10" row. It is called denormalization, but it is worth doubling the data because it allows to have much simplier queries in future.

    To fetch IDs of users who are both your friends and friends of some userId you can do
    select A.p2 from friends A join friends B on A.p2 = B.p1 
    where A.p1 = <userId> and A.p2 != <myId> and B.p2 = <myId>
    You may extend the query to fetch user data instead of only IDs:
    select C.* from friends A join friends B on A.p2 = B.p1 join C users on C.id = A.p2 
    where A.p1 = <userId> and A.p2 != <myId> and B.p2 = <myId>
    Now if you remove last condition from the query and select extra field, you will obtain all users that are friends of a userId:
    select C.*, B.p2 myflag 
    from friends A join friends B on A.p2 = B.p1 join users C on C.id = A.p2 
    where A.p1 = <userId> and A.p2 != <myId>
    To tell if the user is your friend also you need to check if $users->myflag == $myId. You may use IF() function right in the query to select myflag as ready to use boolean field, but it doesn't give neither any advantage, neither disadvantage compared to checking the equality in application/script.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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