Reputation: 216
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"')
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
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