Andrew Mellor
Andrew Mellor

Reputation: 216

Laravel relationship through an array of JSON objects

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:


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:

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

Upvotes: 0

Views: 3460

Answers (1)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25936

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

Upvotes: 1

Related Questions