[Proposal] Relationship with JSON foreign key

[darron1217]

From laravel/framework#15465

I have recently been very pleased with MYSQL's native JSON support Nowadays, clients require higher flexiblility on application. So we needed to establish a flexible relationship between the models.

I imagined flexible relationships like this:

class User extends Model
{
    /**
     * Get the users's preferred locale.
    /*
    public function locale()
    {
        return $this->belongsTo('App\Locale', 'preferences->locale_id', 'id');
    }
}

As @themsaid mentioned, this can lead us to "unclear relationships". Despite that, I thought the benefit is bigger than disadvantage, so I suggested this feature

[Patryk27]

How would you implement inverse of this relationship on databases without native JSON support?

[darron1217]

@Patryk27 I don't have an idea for the non-native support This proposal will share concept with query builder's "JSON where clause"

https://laravel.com/docs/5.6/queries#json-where-clauses

[staudenmeir]

@Patryk27 All the databases supported by Laravel have native JSON support in their latest versions (SQLite requires an extension).

[Patryk27]

My bad, I've just checked and it seems you are right :-)

I still think though that relational data should be modeled as a relation - in case of the OP's situation, user_preferences table with locale_id column wouldn't hurt.

[sisve]

@staudenmeir That sounds like you decided to define "current versions" as "newest versions". However, all versions of database engines that Laravel supports does not have native json support. And are you sure that sqlite supports json natively?

[staudenmeir]

@sisve I updated my comment after I posted it ;-)

[sisve]

... why does GitHub automatically load new posts, but not edits ...

Anyhow, this sounds doable if we're just talking about querying. A join can be on any criteria, so it should work.

I see a few steps we need to take;

1. Build it where supported
2. Add clear error messages where it is not supported.
3. Documentation regarding how the querying works.
4. Documentation regarding indexing json fields and performance.
5. Documentation regarding how this is not a "real" foreign key and there's no data consistency enforced on a database level.

On a slightly related note, we can probably enforce foreign keys by extracting the json value into a persisted calculated column, and put a real foreign key on that one.

[staudenmeir]

Here's a prototype (without tests): https://github.com/staudenmeir/framework/commit/8c0fc03e01cf02999854910303e5833970cb3110

It supports BelongsTo, HasOne, HasMany, HasManyThrough, MorphTo, MorphOne and MorphMany. Since we already support JSON queries (except on SQLite) and the mutation of JSON attributes, the required changes are surprisingly small.

I don't think that JSON support makes a lot of sense for BelongsToMany and MorphToMany with pivot tables. We could use JSON arrays instead and implement a User ↔ Role relationship with users.role_ids. This would require a new relationship type (name?) and use whereJsonContains() for the reverse direction. A downside of this solution is the missing support for foreign keys (AFAIK).

[staudenmeir]

I released the code as a package: https://github.com/staudenmeir/eloquent-json-relations

[raiika]

try this

public function getAttribute($key)
{
    [$key, $path] = preg_split('/(->|\.)/', $key, 2) + [null, null];

    return data_get(parent::getAttribute($key), $path);
}

then you can do this

$this->belongsTo(Post::class, 'data->post_id')
$this->belongsTo(Post::class, 'data.post_id')

and also this as an additional helper

$notification->{'data->title'}

I haven't test it on other relationship, but belongsTo works!

[ronaldzulbaran]

Perfect solution! [belongsTo]

[coleshirley]

I did some tests and it seems like the base installation of laravel supports having foreign keys in json columns in HasMany relationships as follows:

Models:

class User extends model
{
    public function posts()
    {
        return $this->hasMany(Post::class, 'properties->user');
    }
}

class Post extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class, 'properties->user');
    }
}

Migrations:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreatePostsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->json('properties')->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('posts');
    }
}

Assuming that I have a User model and a Post model with {"user": 1} in the properties column the following happens:

Test Route:

Route::get('/', function () {
    dd(
        User::with('posts')->first(),   // returns the user with an empty collection as the posts relation
        User::first()->posts,       // actually returns the collection of posts
        Post::with('user')->first(),   // returns the post with null in the user relation
        Post::first()->user,      // returns null
    );
});

I did some digging and found out that the reason this happens is because there is currently no way to retrieve a json attribute on a model using object syntax. i.e. behind the scenes in the BelongsTo relationship there is the addContstraints function as follows:

/**
     * Set the base constraints on the relation query.
     *
     * @return void
     */
    public function addConstraints()
    {
        if (static::$constraints) {
            // For belongs to relationships, which are essentially the inverse of has one
            // or has many relationships, we need to actually query on the primary key
            // of the related models matching on the foreign key that's on a parent.
            $table = $this->related->getTable();

            $this->query->where($table.'.'.$this->ownerKey, '=', $this->child->{$this->foreignKey});     
            // in this situation this is $this->child->{'properties->user'}
        }
    }

If you try to call $model->{'properties->user'} it returns null

The reason that HasOneOrMany type relations also fail is because when the relationships load and the framework attemps to match the loaded models to their parents the same object syntax is used

/**
     * Build model dictionary keyed by the relation's foreign key.
     *
     * @param  \Illuminate\Database\Eloquent\Collection  $results
     * @return array
     */
    protected function buildDictionary(Collection $results)
    {
        $foreign = $this->getForeignKeyName();

        return $results->mapToDictionary(function ($result) use ($foreign) {
            return [$this->getDictionaryKey($result->{$foreign}) => $result];  // $result->{'properites->user'} returns null
        })->all();
    }

Interestingly enough setting a json value using this object syntax does work:

$post->{'properties->user'} = 1;
dump($post); 
/*
App\Models\Post {#4094
     id: 1,
     properties: "{"user":1}",
     created_at: "2021-06-21 19:37:15",
     updated_at: "2021-06-21 19:37:15",
   }
*/

I submitted a bug report about the json attribute retrieval using the $model->{$key} syntax. Once that functionality is added laravel should be able to support json foreign keys natively

https://github.com/laravel/framework/issues/37767