Sergio Sergio - 6 months ago 385
PHP Question

Order By before Group By using Eloquent (Laravel)

I have a "messages" table with the following columns

CREATE TABLE `messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fromId` int(11) NOT NULL,
`toId` int(11) NOT NULL,
`message` text NOT NULL,
`status` int(11) NOT NULL,
`device` varchar(100) NOT NULL,
`createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=latin1;


I'm trying to get all messages where 'toId' = $id and grouping by fromId. The problem is that the "message" shown on the results is the first ones, not the latest ones. I tried ordering by createdAt but it's not working.

How can I order by "createdAt" prior to querying and grouping the results? I want to do this in the laravel way using Eloquent.

My query:

$chats = Message::with('sender','recipient')
->where('toId',$id)
->orderBy('createdAt')
->groupBy('fromId')
->paginate(10)

Answer

I found a way to do that! Basically, creating a subquery and running it before, so that results are ordered as expected and grouped after.

Here is the code:

$sub = Message::orderBy('createdAt','DESC');

$chats = DB::table(DB::raw("({$sub->toSql()}) as sub"))
    ->where('toId',$id)
    ->groupBy('fromId')
    ->get();