Order to group When using Eloquent (Laravel)

Question

I have a “posts” 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 am trying to get all messages where 'toId' = $ id and grouping byId. The problem is that the “message” shown on the results is the first, not the last. I tried sorting by createdAt, but it does not work.

How can I order "createdAt" before querying and grouping the results? I want to do this with larvel using Eloquent.

My request:

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

Answer 1

I found a way to do this! Basically, we create a subquery and run it earlier so that the 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(); 

Answer 2

I just needed to do something similar with the message model. What worked for me, applied the unique method in the returned eloquent collection.

 Model::where('toId', $id) ->orderBy('createdAt', 'desc') ->get() ->unique('fromId'); 

The request will return all messages sorted using createdAt , and the unique method will reduce it to one message for each fromId . This is clearly not as efficient as using the database directly, but in my case I have additional query restrictions.

In addition, there are many useful methods for working with these collections: https://laravel.com/docs/5.2/collections#available-methods

Answer 3

This work for me: (you can remove the order created by At in case createAt increases along with id)

 DB::select(DB::raw('select * from (select * from messages group by fromId desc) m order by m.createdAt')); 

Answer 4

It should be something like that:

 Message::whereToId($id)->groupBy('fromId')->latest('createdAt')->first(); 

Update

After viewing the request that you added, you just need to add the direction to the orderBy function, for example:

 $chats = Message::with('sender','recipient') ->select(DB::raw('*, max(createdAt) as createdAt')) ->where('toId',$id) ->orderBy('createdAt', 'desc') ->groupBy('fromId') ->paginate(10)