Laravel Eloquent relationships with JSON keys
This Laravel Eloquent extension adds support for JSON foreign keys to
BelongsTo,
HasOne,
HasMany,
HasOneThrough,
HasManyThrough,
MorphTo,
MorphOneand
MorphManyrelationships.
Database |
Laravel |
---|---|
MySQL 5.7+ | 5.5.29+ |
MariaDB 10.2+ | 5.8+ |
PostgreSQL 9.3+ | 5.5.29+ |
SQLite 3.18+ | 5.6.35+ |
SQL Server 2016+ | 5.6.25+ |
composer require staudenmeir/eloquent-json-relations:"^1.1"
In this example,
Userhas a
BelongsTorelationship with
Locale. There is no dedicated column, but the foreign key (
locale_id) is stored as a property in a JSON field (
users.options):
class User extends Model { use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;protected $casts = [ 'options' => 'json', ]; public function locale() { return $this->belongsTo('App\Locale', 'options->locale_id'); }
}
class Locale extends Model { use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;
public function users() { return $this->hasMany('App\User', 'options->locale_id'); }
}
Remember to use the
HasJsonRelationshipstrait in both the parent and the related model.
On MySQL, MariaDB and SQL Server you can still ensure referential integrity with foreign keys on generated/computed columns.
Laravel migrations support this feature on MySQL/MariaDB:
Schema::create('users', function (Blueprint $table) { $table->bigIncrements('id'); $table->json('options'); $locale_id = DB::connection()->getQueryGrammar()->wrap('options->locale_id'); $table->unsignedInteger('locale_id')->storedAs($locale_id); $table->foreign('locale_id')->references('id')->on('locales'); });
Laravel migrations (5.7.25+) also support this feature on SQL Server:
Schema::create('users', function (Blueprint $table) { $table->bigIncrements('id'); $table->json('options'); $locale_id = DB::connection()->getQueryGrammar()->wrap('options->locale_id'); $locale_id = 'CAST('.$locale_id.' AS INT)'; $table->computed('locale_id', $locale_id)->persisted(); $table->foreign('locale_id')->references('id')->on('locales'); });
There is a workaround for older versions of Laravel.
The package also introduces two new relationship types:
BelongsToJsonand
HasManyJson
On Laravel 5.6.25+, you can use them to implement many-to-many relationships with JSON arrays.
In this example,
Userhas a
BelongsToManyrelationship with
Role. There is no pivot table, but the foreign keys are stored as an array in a JSON field (
users.options):
By default, the relationship stores pivot records as an array of IDs:
class User extends Model { use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;protected $casts = [ 'options' => 'json', ]; public function roles() { return $this->belongsToJson('App\Role', 'options->role_ids'); }
}
class Role extends Model { use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;
public function users() { return $this->hasManyJson('App\User', 'options->role_ids'); }
}
On the side of the
BelongsToJsonrelationship, you can use
attach(),
detach(),
sync()and
toggle():
$user = new User; $user->roles()->attach([1, 2])->save(); // Now: [1, 2]$user->roles()->detach([2])->save(); // Now: [1]
$user->roles()->sync([1, 3])->save(); // Now: [1, 3]
$user->roles()->toggle([2, 3])->save(); // Now: [1, 2]
You can also store pivot records as objects with additional attributes:
class User extends Model { use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;protected $casts = [ 'options' => 'json', ]; public function roles() { return $this->belongsToJson('App\Role', 'options->roles[]->role_id'); }
}
class Role extends Model { use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;
public function users() { return $this->hasManyJson('App\User', 'options->roles[]->role_id'); }
}
Here,
options->rolesis the path to the JSON array.
role_idis the name of the foreign key property inside the record object:
$user = new User; $user->roles()->attach([1 => ['active' => true], 2 => ['active' => false]])->save(); // Now: [{"role_id":1,"active":true},{"role_id":2,"active":false}]$user->roles()->detach([2])->save(); // Now: [{"role_id":1,"active":true}]
$user->roles()->sync([1 => ['active' => false], 3 => ['active' => true]])->save(); // Now: [{"role_id":1,"active":false},{"role_id":3,"active":true}]
$user->roles()->toggle([2 => ['active' => true], 3])->save(); // Now: [{"role_id":1,"active":false},{"role_id":2,"active":true}]
Limitations: On SQLite and SQL Server, these relationships only work partially.
On PostgreSQL, you can improve the query performance with
jsonbcolumns and
GINindexes.
Use this migration when the array of IDs/objects is the column itself (e.g.
users.role_ids):
Schema::create('users', function (Blueprint $table) { $table->bigIncrements('id'); $table->jsonb('role_ids'); $table->index('role_ids', null, 'gin'); });
Use this migration when the array is nested inside an object (e.g.
users.options->role_ids):
Schema::create('users', function (Blueprint $table) { $table->bigIncrements('id'); $table->jsonb('options'); $table->rawIndex('("options"->\'role_ids\')', 'users_options_index')->algorithm('gin'); // Laravel 7.10.3+ //$table->index([DB::raw('("options"->\'role_ids\')')], 'users_options_index', 'gin'); // Laravel < 7.10.3 });
Please see CONTRIBUTING and CODE OF CONDUCT for details.