Laravel如何基于JSON字段关联查询两张数据表？

嘿，这个场景我之前做项目时刚好遇到过，Laravel里要关联存着关联ID的JSON字段，得借助数据库的JSON函数来配合join操作，我给你拆解下具体怎么弄：

先假设我们有两张表作为示例：

• orders表：核心字段id，以及meta（JSON类型字段，结构类似{"customer_id": 123, "order_note": "xxx"}）
• customers表：核心字段id、name、email

1. 使用查询构建器（Query Builder）链式调用join #

根据你使用的数据库类型，JSON字段的提取语法会略有不同：

针对MySQL数据库 #

MySQL支持->>操作符（或者JSON_EXTRACT函数）来提取JSON字段的内容，我们可以在join的闭包中用DB::raw()来执行原生SQL片段：

$ordersWithCustomers = DB::table('orders')
    // 如果允许订单没有关联客户，用leftJoin替代join
    ->join('customers', function ($join) {
        // 提取orders.meta里的customer_id，和customers.id关联
        $join->on('customers.id', '=', DB::raw('orders.meta->>"$.customer_id"'));
    })
    ->select('orders.id as order_id', 'customers.name', 'customers.email')
    ->get();

也可以用JSON_EXTRACT写法，效果完全一致：

$join->on('customers.id', '=', DB::raw('JSON_EXTRACT(orders.meta, "$.customer_id")'));

针对PostgreSQL数据库 #

PostgreSQL的JSON提取语法是->>（针对JSON类型）或者#>>（针对嵌套路径），写法类似：

$ordersWithCustomers = DB::table('orders')
    ->join('customers', function ($join) {
        $join->on('customers.id', '=', DB::raw('orders.meta->>\'customer_id\''));
    })
    ->select('orders.id as order_id', 'customers.name', 'customers.email')
    ->get();

2. 用Eloquent模型定义关联关系 #

如果用Eloquent模型，也可以直接在模型里定义关联，方便后续复用调用：
比如在Order模型中：

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Order extends Model
{
    protected $fillable = ['meta'];
    protected $casts = ['meta' => 'array']; // 自动把JSON字段转成PHP数组

    public function customer(): BelongsTo
    {
        return $this->belongsTo(Customer::class)
            ->whereRaw('customers.id = orders.meta->>"$.customer_id"');
    }
}

之后就可以直接用Eloquent的关联查询：

$order = Order::with('customer')->find(1);
// 直接访问关联的客户信息
echo $order->customer->name;

一些注意事项 #

• 确保JSON字段里的customer_id和customers.id的数据类型一致（比如都是整数），否则可能出现关联匹配失败的情况
• 如果JSON字段可能不存在customer_id键，建议用leftJoin（或者Eloquent的withDefault()）来避免过滤掉无关联的记录
• 不同数据库的JSON函数语法有差异，一定要根据自己的数据库类型调整代码

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