Laravel 6+中基于JSON字段的多表JOIN查询语句实现方法

先理清楚我们的场景：有两张表，Staffs存员工基本信息，Salary里的staffids是JSON数组格式，用来关联多个员工的薪资等级。现在要写一个左连接查询，把每个员工对应的薪资拉出来。

先看两张表的结构：

表：Staffs #

注：原表中Staffs的id有重复的2，应该是笔误，我顺手修正成3了，要是实际业务就是重复的也不影响后续逻辑

表：Salary #

你需要实现的SQL语句（我修正了原SQL里的别名笔误，把b改成了s，避免混淆）：

SELECT a.id, a.name, s.scale 
FROM staffs a 
LEFT JOIN salary s ON JSON_CONTAINS(s.staffids, JSON_QUOTE(a.id), '$')

接下来给你两种Laravel里的实现方式，按需选择：

方式一：直接用查询构造器写 #

Laravel的查询构造器支持原生SQL表达式，直接把JSON关联条件塞进去就行：

$results = DB::table('staffs as a')
    ->select('a.id', 'a.name', 's.scale')
    ->leftJoin('salary as s', function ($join) {
        // 用DB::raw包裹原生JSON函数，让Laravel直接执行
        $join->on(DB::raw('JSON_CONTAINS(s.staffids, JSON_QUOTE(a.id), \'$\')'), '=', DB::raw(1));
    })
    ->get();

这里要注意两点：

• JSON_QUOTE(a.id)是必须的，因为JSON数组里的元素是字符串类型，得把数字id转成JSON字符串才能匹配上
• JSON_CONTAINS返回的是1或0，所以要和1做相等判断，让查询条件生效

方式二：封装成Eloquent模型关联推荐用这种，更符合Laravel风格 #

如果已经创建了对应的Eloquent模型（比如Staff和Salary），可以在模型里定义关联关系，以后用起来更方便：

先在Staff模型里定义关联：

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Staff extends Model
{
    protected $table = 'staffs'; // 如果模型名和表名对应，这行也可以省略

    public function salary()
    {
        return $this->hasOne(Salary::class)
            ->whereRaw('JSON_CONTAINS(staffids, JSON_QUOTE(?), \'$\')', [$this->id]);
    }
}

然后查询的时候就可以直接预加载关联：

// 获取所有员工及其对应的薪资
$staffs = Staff::with('salary')->select('id', 'name')->get();

要是你需要一对多的关联（比如一个员工对应多个薪资等级），把hasOne改成hasMany就行。

额外提醒 #

确保你的MySQL版本在5.7及以上，JSON_CONTAINS是这个版本开始支持的，而Laravel 6+要求的MySQL版本刚好满足这个条件，所以不用担心兼容性问题。

内容的提问来源于stack exchange，提问作者Khasruzzaman Milon Mollick