Order By before Group By using Eloquent (Laravel)

Question

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)

Can you maybe show us what your query in Eloquent looks like now? Thanks! — Bananam00n, Aug 06 '15 at 14:09

score 51 · Answer 1 · edited May 14 '16 at 20:12

51

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

edited May 14 '16 at 20:12

doug65536

6,562
3
43
53

answered May 14 '16 at 19:50

ryantbrown

601
1
6
8

1

You just saved me! – Elad Mar 31 '19 at 12:12
That is great. It is very helpful for me – A.A Noman Feb 25 '20 at 07:56
gracias . . . . – Muhamad Rafi Pamungkas Dec 11 '20 at 08:57
Not useful if the output is limited. In that case, the result count will vary. – Mike Aug 13 '21 at 13:38

score 12 · Accepted Answer · answered Aug 06 '15 at 19:19

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();

score 10 · Answer 3 · answered Nov 06 '19 at 12:30

10

Try this query :

$chats = Message::with('sender','recipient')
->where('toId',$id)
->whereRaw('id IN (select MAX(id) FROM messages GROUP BY fromId)')
->orderBy('createdAt','desc')
->paginate(10)

answered Nov 06 '19 at 12:30

Yasin Patel

5,624
8
31
53

lowerends · Answer 4 · 2015-08-06T14:32:56.187

3

It should be something along this:

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

Update

After seeing the query that you've added, you probably just need to add a direction to the orderBy function, like this:

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

edited Aug 06 '15 at 14:32

answered Aug 06 '15 at 14:13

lowerends

5,469
2
24
36

1

Thanks for the help but when I do like this, the message field shows the first message of the group. I want to show the latest one (ordered by createdAt or ID DESC). – starbolt Aug 06 '15 at 14:14
I updated my answer again based on the query that you added to your question. – lowerends Aug 06 '15 at 14:22
1

That really doesn't work. The orderBy is run after the group, so it will order the groups, not the records inside the group.. you got it? I think I need to orderby first and group after, but laravel doesn't seem to allow me to choose to do this. – starbolt Aug 06 '15 at 14:24
Ah, yes, it's more of a limitation of how the MySQL orderBy and groupBy functions work. To work around it, you need to select the max value of the column you want to order by. I've update the query. I hope this helps you out. – lowerends Aug 06 '15 at 14:34
That's just the way group by works. Each `fromId` has multiple `createdAt`s so it's not dependable to order by that column. Instead of `orderBy`, try `orderByRaw('max(createdAt) desc')`. – user1669496 Aug 06 '15 at 14:38
I tried both solutions: select with DB::RAW and orderByRaw. Both return the first message in the group. The orderBy is run after (when results are already grouped in the ASCending order). – starbolt Aug 06 '15 at 14:55
Hmm, I think you may be misunderstanding the goal of `groupBy`, as this will effectively return only one row per `fromId`. Is that not what you are after? – lowerends Aug 06 '15 at 15:00
This table shows all messages that the recipient (toId) received from other users (fromId). I want to return one record per sender (fromId), and this is indeed working. Basically, grouping by fromId will show one message per sender (fromId). BUT, the record choosen by MySQL is the first record for that sender and I want the latest record (so I get the latest message that user sent). Your solution is grouping correctly, but each group shows the first message from the user, not the latest. – starbolt Aug 06 '15 at 15:05

score 2 · Answer 5 · answered Nov 15 '16 at 09:13

2

This work for me: (you can remove the order by createdAt in case createAt increase along with id)

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

answered Nov 15 '16 at 09:13

Alex

359
3
15

score 1 · Answer 6 · edited Jun 01 '20 at 06:21

1

 $data = MCCAddMilkEntry::where('coming_from','=','society')
        ->whereRaw('id = (select max(`id`) from mcc_add_milk_entry)')->get();

edited Jun 01 '20 at 06:21

David Buck

3,752
35
31
35

answered Jun 01 '20 at 05:41

rinka paul

11
2

Try This , Working For Me – rinka paul Jun 01 '20 at 05:42
3

While this code may resolve the OP's issue, it is best to include an explanation as to how your code addresses the OP's issue. In this way, future visitors can learn from your post, and apply it to their own code. SO is not a coding service, but a resource for knowledge. Also, high quality, complete answers are more likely to be upvoted. These features, along with the requirement that all posts are self-contained, are some of the strengths of SO as a platform, that differentiates it from forums. You can edit to add additional info &/or to supplement your explanations with source documentation. – SherylHohman Jun 01 '20 at 22:50

score 0 · Answer 7 · edited Jan 19 '22 at 15:44

@Sergio your answer is good but what if someone wanted to select a related model along with....?

Answer : I have tested every single function but I didn't fetch the correct data. unique() gives data with associated array. groupBy() gives better but picking first message.

Solution for Latest inbox messages. My case:

$data = ChatMessage::where('incoming_msg_id', auth()->user()->id)->orWhere('outgoing_msg_id', auth()->user()->id)->latest('msg_id')->with('messageable')->get();
    $inbox = [];
    $itemIds = [];
    foreach ($data as $key => $value) {
        if (!in_array($value['messageable_id'].$value['msg_for'], $itemIds)) {
            array_push($inbox,$value);
            array_push($itemIds,$value['messageable_id'].$value['msg_for']);
        }
    }
    return $this->sendResponse($inbox,'Inbox');

Your case:

$chats = Message::where('toId',$id)->orWhere('fromId', $id)->latest('id')->with('sender','recipient')->get();
    $inbox = [];
    $fromIds = [];
    foreach ($chats as $key => $value) {
        if (!in_array($value['fromId'], $fromIds)) {
            array_push($inbox,$value);
            array_push($fromIds,$value['fromId']);
        }
    }
    return $this->sendResponse($inbox,'Inbox');

Order By before Group By using Eloquent (Laravel)

7 Answers7

Linked