Eloquent JSON Relations

To maintain referential integrity for foreign keys stored in JSON, you can use generated/computed columns. This allows you to apply standard database foreign key constraints to values inside a JSON field.

MySQL / MariaDB

Use storedAs with a wrapped JSON path in your migration:

$table->json('options');
$locale_id = DB::connection()->getQueryGrammar()->wrap('options->locale_id');
$table->unsignedBigInteger('locale_id')->storedAs($locale_id);
$table->foreign('locale_id')->references('id')->on('locales');

SQL Server

Use computed with a CAST to the appropriate type and ensure it is persisted():

$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');

The package provides BelongsToJson and HasManyJson to handle many-to-many relationships where the pivot data is stored as a simple array of IDs within a JSON field.

Include the \Staudenmeir\\EloquentJsonRelations\\HasJsonRelationships trait in both models. The BelongsToJson relationship supports standard pivot methods: attach(), detach(), sync(), and toggle().

class User extends Model
{
    use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;

    protected $casts = [
       'options' => 'json',
    ];
    
    public function roles(): \Staudenmeir\EloquentJsonRelations\Relations\BelongsToJson
    {
        return $this->belongsToJson(Role::class, 'options->role_ids');
    }
}

class Role extends Model
{
    use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;

    public function users(): \Staudenmeir\EloquentJsonRelations\Relations\HasManyJson
    {
        return $this->hasManyJson(User::class, 'options->role_ids');
    }
}

// Usage on BelongsToJson side:
$user->roles()->attach([1, 2])->save();
$user->roles()->detach([2])->save();
$user->roles()->sync([1, 3])->save();
$user->roles()->toggle([2, 3])->save();

You can define BelongsTo and hasMany relationships where the foreign key is stored within a JSON field instead of a dedicated column. To do this, include the \[Staudenmeir\\EloquentJsonRelations\\HasJsonRelationships] trait in both the parent and the related model. When defining the relationship, use the -> syntax to specify the path to the key within the JSON object.

class User extends Model
{
    use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;

    protected $casts = [
        'options' => 'json',
    ];

    public function locale()
    {
        // Use the path to the foreign key inside the JSON field
        return $this->belongsTo(Locale::class, 'options->locale_id');
    }
}

class Locale extends Model
{
    use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;

    public function users()
    {
        return $this->hasMany(User::class, 'options->locale_id');
    }
}

If you need to store additional attributes alongside your foreign keys in a JSON array (e.g., a pivot record with extra metadata), use the [] syntax in the relationship path.

In the BelongsToJson definition, the path should point to the array, and the key name should be the property inside the object.

Note: These relationships only work partially on SQLite and SQL Server.

Example Path: options->roles[]->role_id where options->roles is the array and role_id is the key inside each object.

class User extends Model
{
    use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;

    protected $casts = [
       'options' => 'json',
    ];
    
    public function roles(): \Staudenmeir\EloquentJsonRelations\Relations\BelongsToJson
    {
        return $this->belongsToJson(Role::class, 'options->roles[]->role_id');
    }
}

// Usage:
$user->roles()->attach([1 => ['active' => true], 2 => ['active' => false]])->save();
// Result: [{"role_id":1,"active":true},{"role_id":2,"active":false}]

The project provides a docker-compose.yml file to orchestrate a testing environment containing multiple PHP versions and various database engines. This allows for testing Eloquent JSON relations across different runtime and database combinations.

Available Services

PHP Runtimes

The configuration includes pre-built images for different PHP versions:

• php8.3 (image: ghcr.io/staudenmeir/php:8.3)
• php8.4 (image: ghcr.io/staudenmeir/php:8.4)
• php8.5 (image: ghcr.io/staudenmeir/php:8.5)

Database Engines

You can run tests against the following databases:

• MySQL: mysql:latest
• MariaDB: mariadb:latest
• PostgreSQL: postgres:latest
• SQL Server: mcr.microsoft.com/mssql/server:2022-latest

services:
  php8.3:
    image: ghcr.io/staudenmeir/php:8.3
    # ...
  mysql:
    image: 'mysql:latest'
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: test