Laravel多对多pivot中间表带额外字段如何查询符合多关联条件的用户

现有表结构下的最优查询方案 #

你不需要拉全量用户过滤，直接用Eloquent的whereHas做多条件关联查询即可，仅需1次数据库查询即可拿到符合要求的结果。

第一步：先在User模型中定义分类型的国家关联 #

// app/Models/User.php
class User extends Model
{
    // 原籍国 type=1
    public function originCountry()
    {
        return $this->belongsToMany(Country::class)
            ->wherePivot('type', 1);
    }

    // 意向访问国1 type=2
    public function intentionCountryFirst()
    {
        return $this->belongsToMany(Country::class)
            ->wherePivot('type', 2);
    }

    // 意向访问国2 type=3
    public function intentionCountrySecond()
    {
        return $this->belongsToMany(Country::class)
            ->wherePivot('type', 3);
    }

    // 意向访问国3 type=4
    public function intentionCountryThird()
    {
        return $this->belongsToMany(Country::class)
            ->wherePivot('type', 4);
    }
}

第二步：编写查询逻辑 #

我们假设国家表的name字段存储国家名称，如果你是用国家ID查询直接替换条件即可：

$targetUsers = User::whereHas('originCountry', function ($query) {
        $query->where('name', '加拿大');
    })
    ->whereHas('intentionCountryFirst', function ($query) {
        $query->where('name', '英国');
    })
    ->whereHas('intentionCountrySecond', function ($query) {
        $query->where('name', '法国');
    })
    ->whereHas('intentionCountryThird', function ($query) {
        $query->where('name', '新西兰');
    })
    ->get();

这个方法底层会转换成带4个EXISTS子查询的SQL语句，直接在数据库层面完成所有过滤，性能远高于拉全量后集合过滤，完全满足你的需求。

可选的架构优化方案 #

因为你的业务场景里用户关联的国家数量是固定的4个，你也可以选择把中间表的字段冗余到用户表中，进一步提升查询性能：
修改users表，新增4个外键字段：

• origin_country_id 关联countries表id，对应原type=1
• intention_country1_id 关联countries表id，对应原type=2
• intention_country2_id 关联countries表id，对应原type=3
• intention_country3_id 关联countries表id，对应原type=4
  这种方案下查询会更简单，不需要关联中间表，直接单表过滤即可：

$targetUsers = User::where('origin_country_id', $canadaId)
    ->where('intention_country1_id', $ukId)
    ->where('intention_country2_id', $franceId)
    ->where('intention_country3_id', $newZealandId)
    ->get();

该方案的优势是查询性能达到最优，缺点是如果后续业务需要调整关联国家的数量，需要修改表结构，适合当前业务规则稳定的场景。

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