How To GroupBy the latest record on the group

Question

What laravel query would I use to groupBy('author_id') but the latest result. Right now everything I try pulls up the first result. I have tried using MAX(id) as id sql function but no luck

it finds the record ok, just finds the first record in the group

Here is my model query that I am using:

public function lastMessages() {
     return $this->hasMany('App\Message', 'recipient_id')->groupBy('author_id')->orderBy('created_at', 'DESC');
}

I have also tried:

public function lastMessages() {
     return $this->hasMany('App\Message', 'recipient_id')
         ->select(DB::raw('*, max(id) as id))
         ->groupBy('author_id')
         ->orderBy('id', 'desc');
}

I have tried this as well and no luck:

public function lastMessages() {
     return $this->hasMany('App\Message', 'recipient_id')
         ->select(DB::raw('*, max(created_at) as created_at))
         ->groupBy('author_id')
         ->orderBy('created_at', 'desc');
}

All of the above will return the first row of the group author_id. I'm looking to return the last row of every group or the latest row. I have also tried ->latest() and no dice!

Thanks in advance for the help.

My Table:

id project_id author_id recipient_id message created_at updated_at

Answer 1

create a subquery join that gets each author latest message id in this way group by will be no longer need since you are already getting each author latest message id in your join subquery

max(created) is no longer need because you are already getting the latest created message in each group

public function lastMessages() {
    return $this->hasMany('App\Message', 'recipient_id')
         ->select('*')
         ->join(DB::raw('(Select max(id) as id from messages group by author_id) LatestMessage'), function($join) {
            $join->on('messages.id', '=', 'LatestMessage.id');
            })
         ->orderBy('created_at', 'desc');
}

Screen Shot