Laravel relationship through an array of JSON objects

Question

Models:

User which hasMany groups, and hasMany roles through group

and

Group which hasMany users.

These relationships are being stored on the group table, in a column called 'members', which is formatted as follows:

[
    {
        user_id: *user_id_one*
        role_id: *role_id_one*
    },
    {
        user_id: *user_id_two*
        role_id: *role_id_two*
    },
]

Requirements:

The models must be linked through a relationship, so that I can eagerly load them.
I cannot change the data structure because a very large amount of programming has already been done regarding these models and structures.
It must be done using sql version from 5.7
It must be done using php 7.1, 7.2, or 7.3

That I've tried:

I first tried this in the User model:

method:
$this->hasMany('\App\Group', 'members->[*].user_id')

query:
select * from `groups` where json_unquote(json_extract(`groups`.`members`, '$."[*].user_id"')) = ? and json_unquote(json_extract(`groups`.`members`, '$."[*].user_id"')) is not null

Then:

method:
$this->hasMany('\App\Group', 'members->[*].user_id')
or
$this->hasMany('\App\Group', 'members->$[*].user_id')

query:
Column Not Found

The mighty staudenmeir/eloquent-json-relations got me closer with

method:
$this->hasManyJson('\App\Group', 'members->[*].user_id')

query:
select * from `groups` where json_contains(`groups`.`members`, ?, '$."[*].user_id"')

These were all tried with the normal primary key and custom attributes which returned:

"JSON_ARRAY(primary_key)"
"JSON_OBJECT('id',primary_key)"
"['primary_key']"
"{'id': 'primary_key'}"

However these all were escaped with quotes.

Already Considered:

Here is an example of a working attribute

public function getGroupsAttribute()
{
    return Group::whereRaw("JSON_CONTAINS(members->'$[*].user_id', JSON_ARRAY('{$this->id}'))")->get();
}

I figured before I went off and made a custom relationship I would ask here. I could probably do something with a scope to apply something like the following (with a relationship that just returned all groups) but it would be the same amount of work and less clean.

->with(['groups' => function ($query) use ($user) {
   $query->whereRaw("JSON_CONTAINS(members->'$[*].user_id', JSON_ARRAY('{$user->id}'))")
}])

I changed the values of a psuedo-randomly selected members column, for demonstration purposes. Assume the column is valid JSON. — Andrew Mellor
– Andrew Mellor, Commented Jan 30, 2020 at 19:04
"I cannot change the data structure..." cue the meme of David Tennant in the rain, saying "I'm sorry. I'm so, so sorry." — Bill Karwin
– Bill Karwin, Commented Jan 30, 2020 at 19:09
The example of a foreign key reference based on a JSON field is a major part of my presentation How to Use JSON in MySQL Wrong. — Bill Karwin
– Bill Karwin, Commented Jan 30, 2020 at 19:12
@BillKarwin Ik man, if it were up to me I would just have made a pivot table (ノ﹏ヽ) — Andrew Mellor
– Andrew Mellor, Commented Jan 30, 2020 at 19:17

Jonas Staudenmeir · Accepted Answer · 2020-01-31 07:33:54Z

1

With the eloquent-json-relations package, you can implement a groups relationship like this:

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

    public function groups()
    {
       return $this->hasManyJson(Group::class, 'members[]->user_id');
    }
}

class Group extends Model
{
    use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;
}

answered Jan 31, 2020 at 7:33

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel relationship through an array of JSON objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related