SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    checking if two users are friends - query advice

    Hi

    I would just like some advice on designing a mysql query to check if two users are friends.
    Ive got a table 'friends' with columns 'user1' and 'user2'. The difficultly is that a friend connection is represented by one row so if i want to find whether users 45 and 54 are friends i need to search the table for
    user1 = 45 AND user2 = 54 OR user1 = 54 AND user2 = 45.
    This is fine, but i need to integrate this as a JOIN so at the moment ive got something like:
    PHP Code:
    "SELECT photos.url, photos.caption FROM photos
    JOIN friends ON (friend1 = '
    $userid' && friend2 = photos.userid) || (friend1 = photos.userid && friend2 = '$userid') 
    (a simplified example of what i want to do)
    So i only want to get photos of users who i am friends with
    The above query works, but im my Mysql query stats, this counts as a JOIN query that doesnt use indexs and so the select_full_join variable goes up, and as far as i understand i need to avoid this completely.
    What is the best way to go about this? Im sure this isnt an uncommon thing to do or am i going about it completely the wrong way?

    Thanks!

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,083
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    MySQL can't JOIN properly because of the OR in your query.
    To overcome this you can use UNION instead

    Code SQL:
    SELECT photos.url, photos.caption FROM photos JOIN friends ON friend1 = '$userid' && friend2 = photos.userid
    UNION
    SELECT photos.url, photos.caption FROM photos JOIN friends ON friend1 = photos.userid && friend2 = '$userid'

    Of course you need indices on friends.friend1, friends.friend2 and photos.userid
    I'm guessing (or rather, hoping) you defined friends.friend1, friends.friend2 as the primary key of friends. This is okay.

    PS. Union by default removes duplicates. So if you (exactly) have (1,2) and (2,1) in the friends table you only get one result, not two, which is what you want.
    So there is no need to use DISTINCT in some way.

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks - so what i have something like this:
    PHP Code:
    SELECT photos.urlphotos.caption FROM photos JOIN friends ON friend1 '$userid' && friend2 photos.userid
    UNION
    SELECT photos
    .urlphotos.caption FROM photos JOIN friends ON friend1 photos.userid && friend2 '$userid' 
    My actual query is quite a bit more substantial with a number of different joins (this one being the only problematic one) so would i just repeat the entire query after UNION?
    ie.
    PHP Code:
    SELECT something, else
    JOIN ...
    JOIN ...
    JOIN friends ON friend1 '$userid' && friend2 photos.userid
    WHERE
    ...
    UNION
    SELECT something
    , else
    JOIN ...
    JOIN ...
    JOIN friends ON friend1 '$userid' && friend2 photos.userid
    WHERE
    ... 
    And yes i have defined friend1 and friend2 as unique...

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,083
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by sheardben View Post
    My actual query is quite a bit more substantial with a number of different joins (this one being the only problematic one) so would i just repeat the entire query after UNION?
    Not entirely, you need to swap friend1 and friend2 in the second query to handle both sides of the relation, i.e.

    Code SQL:
    SELECT something, ELSE
    JOIN ...
    JOIN ...
    JOIN friends ON friend1 = '$userid' && friend2 = photos.userid
    WHERE...
    UNION
    SELECT something, ELSE
    JOIN ...
    JOIN ...
    JOIN friends ON friend2 = '$userid' && friend1 = photos.userid
    WHERE...

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh sorry, meant to change it - i just copied and pasted

    Thanks very much though!

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,083
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by sheardben View Post
    And yes i have defined friend1 and friend2 as unique...
    You have defined the combination of the two as unique I hope, otherwise I can only have 2 friends...

  7. #7
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    You have defined the combination of the two as unique I hope, otherwise I can only have 2 friends...
    Yip...


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
  •