Laravel中计算两个集合中共同receiver_id的总数

我有两个来自同一模型的集合C1和C2，集合结构包含id, sender_id, receiver_id, status字段：

• C1：当前用户（sender_id为auth()->id()）且status为'added'的friendships记录
• C2：指定用户（sender_id为$user->id）且status为'added'的friendships记录

C1实例 #

#items: array:3 [▼
  0 => {#1417 ▼
    +"id": 21
    +"sender_id": 19
    +"receiver_id": 13
    +"status": "added"
    +"created_at": "2022-08-09 19:28:24"
    +"updated_at": "2022-08-09 19:28:24"
  }
  1 => {#1416 ▼
    +"id": 22
    +"sender_id": 19
    +"receiver_id": 11
    +"status": "added"
    +"created_at": "2022-08-09 19:28:28"
    +"updated_at": "2022-08-09 19:28:28"
  }
  2 => {#1419 ▼
    +"id": 23
    +"sender_id": 19
    +"receiver_id": 12
    +"status": "added"
    +"created_at": "2022-08-09 19:28:32"
    +"updated_at": "2022-08-09 19:28:32"
  }
]
#escapeWhenCastingToString: false

C2实例 #

#items: array:3 [▼
  0 => {#1407 ▼
    +"id": 24
    +"sender_id": 18
    +"receiver_id": 13
    +"status": "added"
    +"created_at": "2022-08-09 19:28:52"
    +"updated_at": "2022-08-09 19:28:52"
  }
  1 => {#1398 ▼
    +"id": 25
    +"sender_id": 18
    +"receiver_id": 11
    +"status": "added"
    +"created_at": "2022-08-09 19:28:57"
    +"updated_at": "2022-08-09 19:28:57"
  }
  2 => {#1412 ▼
    +"id": 26
    +"sender_id": 18
    +"receiver_id": 12
    +"status": "added"
    +"created_at": "2022-08-09 19:29:01"
    +"updated_at": "2022-08-09 19:29:01"
  }
]
#escapeWhenCastingToString: false

现有方法代码 #

public function mutualFriend(User $user)
{
    $C1 = DB::table('friendships')->where('sender_id', auth()->id())->where('status', 'added')->get();
    $C2 = DB::table('friendships')->where('sender_id', $user->id)->where('status', 'added')->get();
    // 需在此计算共同receiver_id的数量，示例中应为3
}

需求：计算两个集合中共同的receiver_id数量，示例结果为3，需使用pluck()方法处理。

方法一：集合层面处理（基于pluck） #

通过pluck()提取两个集合的receiver_id，再利用集合的交集方法统计数量：

public function mutualFriend(User $user)
{
    // 直接提取receiver_id集合，减少内存占用
    $c1Receivers = DB::table('friendships')
        ->where('sender_id', auth()->id())
        ->where('status', 'added')
        ->pluck('receiver_id');
        
    $c2Receivers = DB::table('friendships')
        ->where('sender_id', $user->id)
        ->where('status', 'added')
        ->pluck('receiver_id');

    // 计算交集并统计数量
    $mutualCount = $c1Receivers->intersect($c2Receivers)->count();

    return $mutualCount;
}

也可以转成数组后用原生PHP函数处理：

public function mutualFriend(User $user)
{
    $C1 = DB::table('friendships')->where('sender_id', auth()->id())->where('status', 'added')->get();
    $C2 = DB::table('friendships')->where('sender_id', $user->id)->where('status', 'added')->get();

    $c1Ids = $C1->pluck('receiver_id')->toArray();
    $c2Ids = $C2->pluck('receiver_id')->toArray();

    $mutualCount = count(array_intersect($c1Ids, $c2Ids));

    return $mutualCount;
}

方法二：数据库层面查询（更高效） #

如果数据量较大，直接在数据库中计算交集比拉取全量数据更高效，推荐使用：

public function mutualFriend(User $user)
{
    $mutualCount = DB::table('friendships as f1')
        ->where('f1.sender_id', auth()->id())
        ->where('f1.status', 'added')
        ->join('friendships as f2', 'f1.receiver_id', '=', 'f2.receiver_id')
        ->where('f2.sender_id', $user->id)
        ->where('f2.status', 'added')
        ->count();

    return $mutualCount;
}

内容的提问来源于stack exchange，提问作者Muhammad Zohaib Nawaz