Eloquent Json Relations

https://github.com/staudenmeir/eloquent-json-relations
Admin
This Laravel Eloquent extension adds support for JSON foreign keys and relationships within JSON...
Tokens:3,021
Snippets:18
Trust Score:8.6
License:MIT
Update:1 year ago
Show doc for...
Tokens:
Raw
### Installing Eloquent JSON Relations via Composer

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This command installs the `staudenmeir/eloquent-json-relations` package using Composer, specifying a compatible version range (`^1.1`). This is the standard installation method for most environments.

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

--------------------------------

### Defining BelongsToJson and HasManyJson Relationships with Composite Keys - PHP

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This example demonstrates how to define `BelongsToJson` and `HasManyJson` relationships using composite keys. It shows passing an array of keys, starting with the JSON key, to match multiple columns for the relationship.

```PHP
class Employee extends Model
{
    public function tasks(): \Staudenmeir\EloquentJsonRelations\Relations\BelongsToJson
    {
        return $this->belongsToJson(
            Task::class,
            ['options->work_stream_ids', 'team_id'],
            ['work_stream_id', 'team_id']
        );
    }
}

class Task extends Model
{
    public function employees(): \Staudenmeir\EloquentJsonRelations\Relations\HasManyJson
    {
        return $this->hasManyJson(
            Employee::class,
            ['options->work_stream_ids', 'team_id'],
            ['work_stream_id', 'team_id']
        );
    }
}
```

--------------------------------

### Installing Eloquent JSON Relations via Composer (PowerShell)

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This command is specifically for installing the `staudenmeir/eloquent-json-relations` package via Composer when using PowerShell on Windows. It includes additional caret escaping (`^^^^1.1`) to correctly handle the version constraint in the PowerShell environment.

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

--------------------------------

### Creating GIN Index for Direct JSONB Array Column (PostgreSQL, PHP)

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This migration creates a `users` table with a `jsonb` column named `role_ids` and applies a GIN index to it. This setup is ideal when the JSON column directly stores an array of IDs or objects, significantly enhancing query performance on PostgreSQL for array containment or existence checks.

```php
Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->jsonb('role_ids');
    $table->index('role_ids')->algorithm('gin');
});
```

--------------------------------

### Performing CRUD Operations on BelongsToJson Relationship (Array of Objects) - PHP

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This example demonstrates how to use `attach()`, `detach()`, `sync()`, and `toggle()` methods on a `BelongsToJson` relationship when pivot records are stored as an array of objects. It shows how to pass additional attributes when attaching or syncing records.

```PHP
$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}]
```

--------------------------------

### Performing CRUD Operations on BelongsToJson Relationship (Array of IDs) - PHP

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This example demonstrates how to use common Eloquent relationship methods (`attach()`, `detach()`, `sync()`, `toggle()`) on a `BelongsToJson` relationship when the pivot records are stored as an array of IDs. Each operation modifies the JSON array in the 'options->role_ids' path.

```PHP
$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]
```

--------------------------------

### Creating GIN Index for Nested JSONB Array (PostgreSQL, PHP)

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This migration defines a `users` table with a `jsonb` column named `options` and creates a GIN index specifically for a nested array `role_ids` within it. This approach is used when the array of IDs is embedded inside an object within the JSON column, optimizing queries that target these nested array elements.

```php
Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->jsonb('options');
    $table->rawIndex('("options"->'role_ids')', 'users_options_index')->algorithm('gin');
});
```

--------------------------------

### Concatenating Deep and JSON Relationships (User to Permission, PHP)

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This PHP code demonstrates how to combine a `BelongsToJson` relationship (`User` to `Role`) with a standard `HasMany` relationship (`Role` to `Permission`) to create a complex deep relationship (`User` to `Permission`). It leverages `hasManyDeepFromRelations` and requires both `staudenmeir/eloquent-has-many-deep` and `staudenmeir/eloquent-json-relations` packages for its functionality.

```php
class User extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;
    use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;

    public function permissions(): \Staudenmeir\EloquentHasManyDeep\HasManyDeep
    {
        return $this->hasManyDeepFromRelations(
            $this->roles(),
            (new Role)->permissions()
        );
    }
    
    public function roles(): \Staudenmeir\EloquentJsonRelations\Relations\BelongsToJson
    {
        return $this->belongsToJson(Role::class, 'options->role_ids');
    }
}

class Role extends Model
{
    public function permissions()
    {
        return $this->hasMany(Permission::class);
    }
}

$permissions = User::find($id)->permissions;
```

--------------------------------

### Defining HasManyThroughJson Relationship (Role to Project, PHP)

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This PHP code defines a `hasManyThroughJson` relationship from the `Role` model to the `Project` model, traversing through the `User` model. It utilizes `JsonKey` to specify that the `role_ids` are nested within the `options` JSON column of the intermediate `User` table, requiring the `staudenmeir/eloquent-has-many-deep` package for functionality.

```php
class Role extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function projects()
    {
        return $this->hasManyThroughJson(
            Project::class,
            User::class,
            new \Staudenmeir\EloquentJsonRelations\JsonKey('options->role_ids')
        );
    }
}
```

--------------------------------

### Defining Reverse HasManyThroughJson Relationship (Project to Role, PHP)

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This PHP code defines the inverse `hasManyThroughJson` relationship, allowing access from the `Project` model back to the `Role` model, also via the `User` model. It explicitly sets the foreign keys and uses `JsonKey` to pinpoint the `role_ids` nested within the `options` column of the `User` table, completing the bidirectional relationship.

```php
class Project extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function roles()
    {
        return $this->hasManyThroughJson(
            Role::class, User::class, 'id', 'id', 'user_id', new JsonKey('options->role_ids')
        );
    }
}
```

--------------------------------

### Creating MySQL Multi-valued Index for Nested JSON Array of IDs - SQL

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This SQL migration snippet demonstrates how to create a multi-valued index on MySQL 8.0.17+ for a JSON array of IDs nested within an object (e.g., `options->role_ids`). This index helps optimize queries involving such nested JSON structures.

```SQL
Schema::create('users', function (Blueprint $table) {
    // ...
    
    // Array of IDs
    $table->rawIndex('(cast(`options`->'$["role_ids"]' as unsigned array))', 'users_role_ids_index');
    
    // Array of objects
    $table->rawIndex('(cast(`options`->'$["roles"][*]."role_id"' as unsigned array))', 'users_roles_index');
});
```

--------------------------------

### Defining BelongsToJson and HasManyJson Relationships (Array of Objects) - PHP

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This snippet shows how to define `BelongsToJson` and `HasManyJson` relationships where pivot records are stored as an array of objects, allowing for additional attributes alongside the foreign key. The path `options->roles[]->role_id` specifies the JSON array and the foreign key property within each object.

```PHP
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');
    }
}

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

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

--------------------------------

### Creating MySQL Multi-valued Index for JSON Array of IDs (Column Itself) - SQL

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This SQL migration snippet shows how to create a multi-valued index on MySQL 8.0.17+ for a JSON column that directly stores an array of IDs. This index improves query performance for relationships based on such arrays.

```SQL
Schema::create('users', function (Blueprint $table) {
    // ...
    
    // Array of IDs
    $table->rawIndex('(cast(`role_ids` as unsigned array))', 'users_role_ids_index');
    
    // Array of objects
    $table->rawIndex('(cast(`roles`->'$[*]."role_id"' as unsigned array))', 'users_roles_index');
});
```

--------------------------------

### Defining BelongsToJson and HasManyJson Relationships (Array of IDs) - PHP

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This snippet illustrates how to define `BelongsToJson` and `HasManyJson` relationships where the pivot records are stored as a simple array of IDs within a JSON column. It shows the necessary trait usage and the `protected $casts` property for the JSON column.

```PHP
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');
    }
}
```

--------------------------------

### Defining HasOneJson Relationship - PHP

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This snippet illustrates how to define a `HasOneJson` relationship, which is used to retrieve a single related instance from a JSON array. It often includes methods like `latest()` to specify which single record to retrieve.

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

    public function latestUser(): \Staudenmeir\EloquentJsonRelations\Relations\HasOneJson
    {
        return $this->hasOneJson(User::class, 'options->roles[]->role_id')
            ->latest();
    }
}
```

--------------------------------

### Defining One-To-Many JSON Relationships in Laravel

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This snippet demonstrates how to establish one-to-many relationships (`BelongsTo` and `HasMany`) in Laravel Eloquent models where the foreign key is stored as a property within a JSON field. It requires the `HasJsonRelationships` trait in both models and casting the JSON field.

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

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

    public function locale()
    {
        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');
    }
}
```

--------------------------------

### Ensuring Referential Integrity with JSON Foreign Keys (MySQL/MariaDB)

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This Laravel migration snippet illustrates how to enforce referential integrity for a JSON foreign key on MySQL/MariaDB. It creates a stored generated column that extracts the `locale_id` from the `options` JSON field, allowing a standard foreign key constraint to be applied.

```PHP
Schema::create('users', function (Blueprint $table) {
    $table->bigIncrements('id');
    $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');
});
```

--------------------------------

### Ensuring Referential Integrity with JSON Foreign Keys (SQL Server)

Source: https://github.com/staudenmeir/eloquent-json-relations/blob/main/README.md

This Laravel migration snippet demonstrates how to enforce referential integrity for a JSON foreign key on SQL Server. It defines a persisted computed column that casts the extracted `locale_id` from the `options` JSON field to an integer, enabling a foreign key constraint.

```PHP
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');
});
```
Tokens:
Raw