命令由组之前通过使用雄辩（Laravel） [英] Order By before Group By using Eloquent (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;

我试图获取所有消息，其中'toId'= $ id并按fromId分组。问题是结果显示的消息是第一个，而不是最新的。我试图通过createdAt进行排序，但是它不起作用。

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.

在查询和分组结果之前，如何通过createdAt进行排序？我想用Laravel的方法，用口才来做这个。

How can I order by "createdAt" prior to querying and grouping the results? I want to do this in the laravel way using Eloquent.

我的查询：

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

推荐答案

我找到了一个办法！基本上，创建一个子查询并运行它，以便结果按预期顺序排列，然后分组。

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.

这是代码：

$sub = Message::orderBy('createdAt','DESC');

$chats = DB::table(DB::raw("({$sub->toSql()}) as sub"))
    ->where('toId',$id)
    ->groupBy('fromId')
    ->get();

这篇关于命令由组之前通过使用雄辩（Laravel）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！