如何从他人粉丝列表识别我关注或关注我的用户？求Laravel/MySQL实现

针对你的需求——作为user_id=4的用户，查看user_id=1的粉丝列表，并明确每个粉丝与你之间的关注状态（是否关注你/你是否关注对方），我整理了MySQL原生查询和Laravel Eloquent两种实现方式：

MySQL 查询语句 #

直接通过两次左连接关联关注表，分别判断粉丝关注你、你关注粉丝的状态：

SELECT 
    f.following_id AS follower_id,
    -- 判断该粉丝是否关注你（user_id=4）
    CASE WHEN uf.leader_id IS NOT NULL THEN 1 ELSE 0 END AS follows_me,
    -- 判断你是否关注该粉丝
    CASE WHEN fu.leader_id IS NOT NULL THEN 1 ELSE 0 END AS i_follow
FROM 
    followings f
-- 左连接：粉丝作为关注者，关注你（user_id=4）的记录
LEFT JOIN 
    followings uf ON uf.leader_id = f.following_id AND uf.following_id = 4
-- 左连接：你作为关注者，关注该粉丝的记录
LEFT JOIN 
    followings fu ON fu.leader_id = 4 AND fu.following_id = f.following_id
WHERE 
    f.leader_id = 1; -- 筛选user_id=1的粉丝

执行后会得到如下结果：

（注：根据你提供的表数据，user_id=2、3都没有关注你，你也没有关注他们；你自己作为粉丝时，不存在自我关注的记录，所以状态都是0）

Laravel Eloquent 实现 #

方式一：查询构建器（推荐，单查询高效） #

直接对应上面的SQL逻辑，用查询构建器实现：

$currentUserId = 4;
$targetUserId = 1;

$followersWithRelations = DB::table('followings as f')
    ->select(
        'f.following_id as follower_id',
        DB::raw('CASE WHEN uf.leader_id IS NOT NULL THEN 1 ELSE 0 END AS follows_me'),
        DB::raw('CASE WHEN fu.leader_id IS NOT NULL THEN 1 ELSE 0 END AS i_follow')
    )
    ->leftJoin('followings as uf', function ($join) use ($currentUserId) {
        $join->on('uf.leader_id', '=', 'f.following_id')
             ->where('uf.following_id', '=', $currentUserId);
    })
    ->leftJoin('followings as fu', function ($join) use ($currentUserId) {
        $join->on('fu.leader_id', '=', $currentUserId)
             ->on('fu.following_id', '=', 'f.following_id');
    })
    ->where('f.leader_id', '=', $targetUserId)
    ->get();

方式二：模型关联（适合需要复用关联逻辑的场景） #

先定义Following模型的关联关系：

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Following extends Model
{
    protected $table = 'followings';
    // 复合主键
    protected $primaryKey = ['leader_id', 'following_id'];
    public $incrementing = false;

    // 粉丝是否关注当前用户的关联
    public function followerFollowsMe($currentUserId)
    {
        return $this->hasOne(Following::class, 'leader_id', 'following_id')
            ->where('following_id', $currentUserId);
    }

    // 当前用户是否关注粉丝的关联
    public function iFollowFollower($currentUserId)
    {
        return $this->hasOne(Following::class, 'following_id', 'following_id')
            ->where('leader_id', $currentUserId);
    }
}

然后查询并处理结果：

$currentUserId = 4;
$targetUserId = 1;

$followers = Following::where('leader_id', $targetUserId)
    ->with([
        'followerFollowsMe' => fn($query) => $query->where('following_id', $currentUserId),
        'iFollowFollower' => fn($query) => $query->where('leader_id', $currentUserId)
    ])
    ->get()
    ->map(function ($follow) {
        return [
            'follower_id' => $follow->following_id,
            'follows_me' => $follow->followerFollowsMe ? 1 : 0,
            'i_follow' => $follow->iFollowFollower ? 1 : 0,
        ];
    });

这种方式需要注意：如果粉丝数量较多，可能会触发N+1查询，建议搭配with预加载优化，或者优先选择查询构建器的方式。

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