max234435 max234435 - 1 month ago 15
PHP Question

How To GroupBy the latest record on the group

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

create a subquery join that gets each author latest message id in this way group by will be no longest 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