phpsqlmysqllaravelrelationship

SQL query to get users who are following someone and being followed back by same person


I have a scenario in which I want to find users who are following user X and user X is following them back.

Look at the following schema: database_table_with_test_values

we have follower_id and following_id, now lets say we want to check if user with id 23 is following someone and they are following him back.

as you can see user 22 is following user 23 back, so we want to get user 22 details from users table such as name and email.

user 23 is following 24 but user 24 is not following user 23 back so we wont include user 24 in our list.

Please help me write the query to get the list of users for above scenario. Thanks

Update

As I needed more info on User. A slow solution I am implementing for now in laravel.

        $UsersIAmFollowing = UserFollowing::where('follower_id', $user->id)->get();
        $UsersFollowingMe = UserFollowing::where('following_id', $user->id)->get();
        $friends = [];
        foreach ($UsersIAmFollowing as $userIamFollowing) {
            foreach ($UsersFollowingMe as $userFollowingMe) {
                if($userIamFollowing->following_id == $userFollowingMe->follower_id){
                    array_push($friends, User::find($userIamFollowing->following_id));
                }
            }
        }

        dd($friends);

Solution

  • select t.id 
          ,t.follower_id    
          ,t.following_id
    from   t join t t2 on t2.following_id = t.follower_id 
             and          t.following_id = t2.follower_id
    
    id follower_id following_id
    7 22 23
    6 23 22

    Fiddle