使用 group by 和 Laravel 获取最新行
即使有多个这样的问题，我也无法让我的查询返回具有最近日期的行和 group by.
Even though there are multiple questions like this I can't get my query to return the row with the most recent date with a group by.
我有下表..
| message_id | from | to | created_at | status
----------------------------------------------
| 1 | 1 | 2 | 2017-04-06 | 1
| 2 | 1 | 2 | 2017-04-07 | 0
| 3 | 3 | 4 | 2017-04-06 | 1
| 4 | 3 | 4 | 2017-04-07 | 0
----------------------------------------------
我正在尝试获取最新日期的行.
and I'm tryin to get the rows with most recent date.
| message_id | from | to | created_at | status
----------------------------------------------
| 2 | 1 | 2 | 2017-04-07 | 0
| 4 | 3 | 4 | 2017-04-07 | 0
目前此查询返回最近日期的行.
Currently this query returns the rows with the last recent date.
$messages = Message::where('to', Auth::id())
->groupBy('from')
->orderBy('created_at', 'DESC')
->paginate(10);
推荐答案
问题是结果集会先分组然后有序.您可以使用嵌套选择来获取您想要的内容.
The problem is that the result set will be first grouped then ordered. You can use nested select to get what you want.
SQL 查询:
SELECT t.* FROM (SELECT * FROM messages ORDER BY created_at DESC) t GROUP BY t.from
使用 Laravel:
With Laravel:
$messages = Message::select(DB::raw('t.*'))
->from(DB::raw('(SELECT * FROM messages ORDER BY created_at DESC) t'))
->groupBy('t.from')
->get();
您只需要添加您的 where() 子句.
You just need to add your where() clauses.
相关文章