CODEWITHSUNDEEP
phpmysqllaravellaravel-5
max234435
max234435

Reputation: 567

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

Upvotes: 4

Views: 9783

Answers (3)

M.Samiei
M.Samiei

Reputation: 51

to groupby the latest record, you can use latest & unique methods like this:

public function lastMessages() {
 return $this->hasMany('App\Message', 'recipient_id')->latest()->get()->unique('author_id');
}

Upvotes: 3

Beginner
Beginner

Reputation: 4153

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

Upvotes: 8

max234435
max234435

Reputation: 567

After banging my head at this for hours and some input from this forum, I got to this which also provides me with a desired result.

public function lastMessages() {
    return $this->hasMany('App\Message', 'recipient_id')
        ->select('*')
        ->whereIn('id', function($q){
            $q->select(DB::raw('MAX(id) FROM messages GROUP BY author_id'));
        });
}

Upvotes: 4

Related Questions