用口语化的方式按前组排序(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.

我试图获取所有'toId' = $id并按fromId分组的消息。问题是，结果显示的“消息”是第一个，而不是最新的。我试着订了，但没有用。

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”进行排序?我想用生动的方式来做这件事。

My query:

我的查询:

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

4 个解决方案

#1

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.

查询将返回createdAt命令的所有消息，惟一方法将每个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

#2

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

这是我的工作:(您可以通过createdAt删除订单，以防随着id的增加而增加createAt)

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

#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:

看到添加的查询后，您可能只需要向orderBy函数添加一个方向，如下所示:

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

#4

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

#1