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}'))")
}])

Answer 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;
}