CODEWITHSUNDEEP
laraveleloquentlumen
martin
martin

Reputation: 13

Get result by joining with JSON Object Key of a column

points table - columns: id, json_column

[
 {"user_id":"1","points":"1"},
 {"user_id":"2","points":"1"},
 {"user_id":"3","points":"0"},
]

users table - columns: id, name

1 | steve
2 | matthew
3 | john

Expected result. All users with points using eloquent

1-steve-1
2-matthew-1
3-john-0

$users=User::all();

Is it possible to get the output using with statement

Edit:

Point model

protected $casts = [
        'json_column' => 'json'
 ];

user model

 public function point()
{
    return $this->hasOne(Point::class, 'json_column->user_id', 'id');
}

Printed query - but it returns null:

select * 
from `points` 
where json_unquote(json_extract(`points`.`json_column`, '$."user_id"')) in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16)

This works if a single json array is in column :

{"user_id":"1","points":"1"}

If it contains array of arrays, code doesn't work

[
 {"user_id":"1","points":"1"},
 {"user_id":"2","points":"1"},
 {"user_id":"3","points":"0"},
]

Edit 2: casted as array. but doesnt working

 protected $casts = [
        'application_data' => 'array'
    ];

Upvotes: 1

Views: 987

Answers (1)

Marwane Ezzaze
Marwane Ezzaze

Reputation: 1057

Yes its possible, assuming you already have User and Point models created:

USER modal

class User extends Model
{
    protected $table = 'users';

    protected $guarded = [];


    public function point()
    {
        return $this->hasOne(Point::class, 'json_column->user_id');
    }
}

POINT modal

class Point extends Model
{
    protected $table = 'points';

    protected $guarded = [];
    protected $casts = [
        'json_column' => 'json'
    ];

}

then you can eager load the users point using with

Upvotes: 1

Related Questions