求编写SQL查询获取与指定用户互关的用户信息

原生SQL查询语句 #

假设用户表名为users，关注关系表名为user_followings，要查询ID为23的用户的互关用户信息，可使用以下两种高效写法：

方式一：自连接查询 #

SELECT u.id, u.name, u.email
FROM users u
JOIN user_followings uf1 ON u.id = uf1.following_id
JOIN user_followings uf2 ON u.id = uf2.follower_id
WHERE uf1.follower_id = 23
  AND uf2.following_id = 23;

方式二：EXISTS子查询 #

SELECT u.id, u.name, u.email
FROM users u
WHERE EXISTS (
    SELECT 1 FROM user_followings uf1
    WHERE uf1.follower_id = 23 AND uf1.following_id = u.id
)
AND EXISTS (
    SELECT 1 FROM user_followings uf2
    WHERE uf2.following_id = 23 AND uf2.follower_id = u.id
);

这两种写法都将逻辑放在数据库层面执行，避免了客户端双重循环的低效操作，同时解决了原代码中User::find带来的N+1查询问题。

Laravel Eloquent优化实现 #

方法一：查询构造器（自连接） #

$userId = $user->id;

$friends = User::query()
    ->join('user_followings as uf1', 'users.id', '=', 'uf1.following_id')
    ->join('user_followings as uf2', 'users.id', '=', 'uf2.follower_id')
    ->where('uf1.follower_id', $userId)
    ->where('uf2.following_id', $userId)
    ->select('users.id', 'users.name', 'users.email')
    ->get();

dd($friends);

方法二：查询构造器（EXISTS子查询） #

$userId = $user->id;

$friends = User::query()
    ->whereExists(function ($query) use ($userId) {
        $query->select(DB::raw(1))
            ->from('user_followings')
            ->where('follower_id', $userId)
            ->whereColumn('following_id', 'users.id');
    })
    ->whereExists(function ($query) use ($userId) {
        $query->select(DB::raw(1))
            ->from('user_followings')
            ->where('following_id', $userId)
            ->whereColumn('follower_id', 'users.id');
    })
    ->select('id', 'name', 'email')
    ->get();

dd($friends);

方法三：模型关联（简洁易读） #

先在User模型中定义关联关系：

// User.php
public function followings()
{
    return $this->belongsToMany(User::class, 'user_followings', 'follower_id', 'following_id');
}

public function followers()
{
    return $this->belongsToMany(User::class, 'user_followings', 'following_id', 'follower_id');
}

然后通过关联交集获取互关用户：

$friends = $user->followings()->whereIn('users.id', $user->followers()->pluck('users.id'))->get();
dd($friends);

内容的提问来源于stack exchange，提问作者Ameer Hamza