CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-5.4
Hkm Sadek
Hkm Sadek

Reputation: 3209

How to to return most recent data using group by and order by

I have seen many posts but nothing is wokring. It seems group by always group the oldest one. I want to group the result and pick the most recent one. Here is my query 

Message::whereIn('conversation_id',$msgs)
             ->orderBy('created_at', 'desc') 
             ->groupBy('conversation_id')
             ->with(array('last_sender'=>function($query){
                    $query->select('id','userName','profilePic', 'firstName','lastName');
              }))
            ->get();

Upvotes: 1

Views: 53

Answers (2)

elegisandi
elegisandi

Reputation: 474

To order by time, try this one:

Message::select(DB::raw('MAX(created_at) AS max_created_at'))
             ->whereIn('conversation_id',$msgs)
             ->orderBy('max_created_at', 'desc') 
             ->groupBy('conversation_id')
             ->with(array('last_sender'=>function($query){
                    $query->select('id','userName','profilePic', 'firstName','lastName');
              }))
            ->get();

Upvotes: 1

Bibhudatta Sahoo
Bibhudatta Sahoo

Reputation: 4894

Try like this

Message::whereIn('conversation_id',$msgs)
             ->with(array('last_sender'=>function($query){
                    $query->select('id','userName','profilePic', 'firstName','lastName');
              }))
             ->orderBy('conversation_id', 'desc') 
             ->groupBy('conversation_id')
            ->get();

Upvotes: 1

Related Questions