php Order By before Group By 使用 Eloquent (Laravel)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31857961/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Order By before Group By using Eloquent (Laravel)
提问by Sergio
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.
我正在尝试获取所有 'toId' = $id 并按 fromId 分组的消息。问题是结果上显示的“消息”是第一个，而不是最新的。我尝试通过 createdAt 订购，但它不起作用。
How can I order by "createdAt" prior to querying and grouping the results? I want to do this in the laravel way using Eloquent.
如何在查询和分组结果之前按“createdAt”排序？我想使用 Eloquent 以 laravel 的方式做到这一点。
My query:
我的查询：
$chats = Message::with('sender','recipient')
->where('toId',$id)
->orderBy('createdAt')
->groupBy('fromId')
->paginate(10)
采纳答案by Sergio
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();
回答by ryantbrown
I just needed to do something similar with a messages model. What worked for me was applying the uniquemethod on the returned eloquent collection.
我只需要用消息模型做一些类似的事情。对我有用的unique是在返回的 eloquent 集合上应用该方法。
Model::where('toId', $id)
->orderBy('createdAt', 'desc')
->get()
->unique('fromId');
The query will return all messages ordered by createdAtand the uniquemethod 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.
查询将返回所有按顺序排序的消息，createdAt并且该unique方法会将其减少为每个 一条消息fromId。这显然不如直接使用数据库那么高效，但在我的情况下，我对查询有进一步的限制。
Also, there are many more useful methods for working with these collections: https://laravel.com/docs/5.2/collections#available-methods
此外，还有更多有用的方法来处理这些集合：https: //laravel.com/docs/5.2/collections#available-methods
回答by lowerends
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 orderByfunction, like this:
看到您添加的查询后，您可能只需要向orderBy函数添加一个方向，如下所示：
$chats = Message::with('sender','recipient')
->select(DB::raw('*, max(createdAt) as createdAt'))
->where('toId',$id)
->orderBy('createdAt', 'desc')
->groupBy('fromId')
->paginate(10)
回答by Alex
This work for me: (you can remove the order by createdAt in case createAt increase along with id)
这对我有用：（您可以通过 createdAt 删除订单，以防 createAt 与 id 一起增加）
DB::select(DB::raw('select * from (select * from messages group by fromId desc) m order by m.createdAt'));
回答by Yasin Patel
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)
