Introduction

This Laravel Eloquent extension adds support for JSON foreign keys to

BelongsTo

HasOne

HasMany

HasOneThrough

HasManyThrough

MorphTo

MorphOne

MorphMany

Compatibility

Installation

composer require staudenmeir/eloquent-json-relations:"^1.1"

Usage

• One-To-Many Relationships
  • Referential Integrity
• Many-To-Many Relationships
  • Array of IDs
  • Array of Objects
  • Query Performance

One-To-Many Relationships

In this example,

User

BelongsTo

Locale

locale_id

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

HasJsonRelationships

Referential Integrity

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.

Many-To-Many Relationships

The package also introduces two new relationship types:

BelongsToJson

HasManyJson

On Laravel 5.6.25+, you can use them to implement many-to-many relationships with JSON arrays.

In this example,

User

BelongsToMany

Role

users.options

Array of IDs

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

BelongsToJson

attach()

detach()

sync()

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]

Array of Objects

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->roles

role_id

$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.

Query Performance

On PostgreSQL, you can improve the query performance with

jsonb

GIN

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
});

Contributing

Please see CONTRIBUTING and CODE OF CONDUCT for details.