How do get Laravel Join to return data like this?

Question

How I can get a data returned from database like this using join of leftJoin or rightJoin?

{
"users": [{
    "id": 129,
    "name": Daniel,
    "messages": [{
        "message_id":1,
        "user_id": 120,
        "subject": "some text A",
        "message": "Some text message A"
    },{
        "message_id":2,
        "user_id": 120,
        "subject": "some text",
        "message": "Some text message S"
    },{
        "message_id":3,
        "user_id": 120,
        "subject": "some text",
        "message": "Some text message S"
    }],
}, {
    "id": 2,
    "user_id": 120,
    "name": "Emmy",
    "messages":[{
        "message_id":25,
        "user_id": 120,
        "subject": "some text C",
        "message": "Some text message C"
    }]

}],
"count": 2
}

This is what I usually use:

            $users = DB::table('users')
            ->leftJoin('messages', function ($join) {
                $join->on('users.id', '=', 'messages.user_id');
            })
            ->get();

The data I get is not what I want, so please let me know what is the best way to do that. Any help appreciated. Thanks

Answer 1

Well, join query dose not returns such as data - it can only get flat data or limit the query results. you should add a relation method like in the User model like this:

 public  function messages()
 {
     return $this->hasMany(Messages::class, 'user_id');
 }

And then reedit the query method chain like this:

$users = User::with('messages')
        ->get();

where with from the User model method will generate the eager load contraint query for related table and will brings to You a Collection of model of Message models to each User model.