Laravel HasMany关联JSON列失效：如何通过data->variable关联模型？

Hey there! I’ve dealt with this exact frustration before—Laravel’s default relationship methods don’t automatically parse JSON path expressions like data->variable when you pass them as foreign/local key arguments. Let’s break down the solutions that actually work:

1. Use whereRaw with Native JSON Query Syntax #

The simplest fix is to bypass the default key matching and define the relationship condition using raw SQL, which lets your database properly interpret the JSON path. Here’s how to do it for MySQL (adjust for PostgreSQL if needed):

public function test()
{
    return $this->hasMany(MultiTenant::getModel('TestModel'))
        // For MySQL 5.7+, the ->> operator gets unquoted JSON values
        ->whereRaw('`value` = `data`->>"$.variable"');
}

If you need broader compatibility (or prefer explicit functions), you can use JSON_UNQUOTE and JSON_EXTRACT:

->whereRaw('`value` = JSON_UNQUOTE(JSON_EXTRACT(`data`, "$.variable"))');

For PostgreSQL, the syntax is a bit different:

->whereRaw('"value" = data->>\'variable\'');

2. Use whereColumn with DB::raw #

A slightly cleaner approach (that still leverages Laravel’s query builder) is to use whereColumn alongside DB::raw to map the JSON value to the foreign key:

use Illuminate\Support\Facades\DB;

public function test()
{
    return $this->hasMany(MultiTenant::getModel('TestModel'))
        ->whereColumn('value', DB::raw('`data`->>"$.variable"'));
}

This tells Laravel to compare the value column directly against the parsed JSON value from data->variable, without treating the JSON path as a literal column name.

Why Your Original Code Failed #

Laravel’s hasMany($related, $foreignKey, $localKey) method expects $localKey to be a plain database column name, not an expression. When you passed data->variable, Laravel tried to match against a column literally named data->variable (which doesn’t exist), hence no results were returned.

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