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)
I just needed to do something similar with a messages model. What worked for me was applying the unique
method on the returned eloquent collection.
Model::where('toId', $id)
->orderBy('createdAt', 'desc')
->get()
->unique('fromId');
The query will return all messages ordered by createdAt
and the unique
method will reduce it down to one message for each fromId
. This is obviously not as performant as using the database directly, but in my case I have further restrictions on the query.
Also, there are many more useful methods for working with these collections: https://laravel.com/docs/5.2/collections#available-methods
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With