Download the PHP package staudenmeir/eloquent-json-relations without Composer

Eloquent JSON Relations

This Laravel Eloquent extension adds support for JSON foreign keys to BelongsTo, HasOne, HasMany, HasOneThrough , HasManyThrough, MorphTo, MorphOne and MorphMany relationships.

It also provides has-many-through relationships with JSON arrays.

Compatibility

• MySQL 5.7+
• MariaDB 10.2+
• PostgreSQL 9.3+
• SQLite 3.38+
• SQL Server 2016+

Installation

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

Use this command if you are in PowerShell on Windows (e.g. in VS Code):

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

Versions

Usage

• One-To-Many Relationships
  • Referential Integrity
• Many-To-Many Relationships
  • Array of IDs
  • Array of Objects
  • HasOneJson
  • Composite Keys
  • Query Performance
• Has-Many-Through Relationships
• Deep Relationship Concatenation

One-To-Many Relationships

In this example, User has a BelongsTo relationship with Locale. There is no dedicated column, but the foreign key (locale_id) is stored as a property in a JSON field (users.options):

Remember to use the HasJsonRelationships trait in both the parent and the related model.

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:

Laravel migrations also support this feature on SQL Server:

Many-To-Many Relationships

The package also introduces two new relationship types: BelongsToJson and HasManyJson

Use them to implement many-to-many relationships with JSON arrays.

In this example, User has a BelongsToMany relationship with Role. There is no pivot table, but the foreign keys are stored as an array in a JSON field (users.options):

Array of IDs

By default, the relationship stores pivot records as an array of IDs:

On the side of the BelongsToJson relationship, you can use attach(), detach(), sync() and toggle():

Array of Objects

You can also store pivot records as objects with additional attributes:

Here, options->roles is the path to the JSON array. role_id is the name of the foreign key property inside the record object:

Limitations: On SQLite and SQL Server, these relationships only work partially.

HasOneJson

Define a HasOneJson relationship if you only want to retrieve a single related instance:

Composite Keys

If multiple columns need to match, you can define a composite key.

Pass an array of keys that starts with JSON key:

Query Performance

MySQL

On MySQL 8.0.17+, you can improve the query performance with multi-valued indexes.

Use this migration when the array is the column itself (e.g. users.role_ids):

Use this migration when the array is nested inside an object (e.g. users.options->role_ids):

MySQL is quite picky about the syntax so I recommend that you check once with EXPLAIN that the executed relationship queries actually use the index.

PostgreSQL

On PostgreSQL, you can improve the query performance with jsonb columns and GIN indexes.

Use this migration when the array of IDs/objects is the column itself (e.g. users.role_ids):

Use this migration when the array is nested inside an object (e.g. users.options->role_ids):

Has-Many-Through Relationships

Similar to Laravel's HasManyThrough, you can define HasManyThroughJson relationships when the JSON column is in the intermediate table (Laravel 9+). This requires staudenmeir/eloquent-has-many-deep.

Consider a relationship between Role and Project through User:

Role → has many JSON → User → has many Project

Install the additional package, add the HasRelationships trait to the parent (first) model and pass the JSON column as a JsonKey object:

The reverse relationship would look like this:

Deep Relationship Concatenation

You can include JSON relationships into deep relationships by concatenating them with other relationships using staudenmeir/eloquent-has-many-deep (Laravel 9+).

Consider a relationship between User and Permission through Role:

User → belongs to JSON → Role → has many → Permission

Install the additional package, add the HasRelationships trait to the parent (first) model and define a deep relationship:

Contributing

Please see CODE OF CONDUCT for details.