Mysql – Nice query to get the friends

MySQL

my table has this structure:

 User_ID | Followed_ID

Example row:

 33 | 36

Means that user 33 follows user 36.

If exists a record like

 36 | 33

then the two users are considered friends.
What's the cleanest way to get all friends of(for example) user 36? Thanks.

Best Answer

It can be done with a simple join (assuming the (user_id, follower_id) combination has a unique constraint):

SELECT 
    f1.followed_id AS friend_id
FROM
    follow AS f1
  JOIN
    follow AS f2
      ON  f1.user_id = f2.followed_id
      AND f1.followed_id = f2.user_id
WHERE
    f1.user_id = 36 ;

Related Solutions

Mysql – How to get a user’s friends’ names

If you want to get the names of the friends for a user perhaps try something like so:

SELECT u.firstname, u.surname 
FROM friend f, user u
WHERE (f.Person1 = 1 OR f.Person2 = 1)

This should get a back a list of names in your user table (providing that is what you have named your friends in the user table) that are friends with userID 1

I'm guessing your friend table contains the foreign key IDs of the friends making up the partnership?

Mysql – Get wall_post and profile data for several users

Here is your original query

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid) 
    AND (default_wall_posts.userid = 2 OR default_wall_posts.userid = 3)
WHERE
    default_wall_posts.userid = 1 AND
    (
        default_wall_posts.userid = 2 AND
        default_wall_posts.userid = 3
    )
ORDER BY
    default_wall_posts.date_created DESC;

The WHERE is impossible to fulfill because default_wall_posts.userid not be 1 and 2 and 3 at the same time.

Try these changes

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid) 
WHERE
    default_wall_posts.userid IN (1,2,3)
ORDER BY
    default_wall_posts.date_created DESC;

Best Answer

Related Solutions

Mysql – How to get a user’s friends’ names

Mysql – Get wall_post and profile data for several users

Related Question