使用 group by 和 Laravel 获取最新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43282161/
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
Get most recent row with group by and Laravel
提问by mattesj
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.
即使有多个这样的问题，我也无法让我的查询返回具有 group by 的最近日期的行。
I have the following table..
我有下表..
| 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);
回答by Ivanka Todorova
The problem is that the result set will be first groupedthen ordered. You can use nested select to get what you want.
问题是结果集将首先分组然后排序。您可以使用嵌套选择来获取您想要的内容。
SQL Query:
SQL查询：
SELECT t.* FROM (SELECT * FROM messages ORDER BY created_at DESC) t GROUP BY t.from
With Laravel:
使用 Laravel：
$messages = Message::select(DB::raw('t.*'))
->from(DB::raw('(SELECT * FROM messages ORDER BY created_at DESC) t'))
->groupBy('t.from')
->get();
You just need to add your where()clauses.
你只需要添加你的where()条款。
回答by sllim.Sam
You may replace groupBy with distinct, as it works in my case.
您可以用 distinct 替换 groupBy，因为它适用于我的情况。
$messages = Message::where('to', Auth::id())
->orderBy('created_at', 'DESC')
->distinct('from')
->paginate(10);
Hope this helps.
希望这可以帮助。
回答by Lewis
You've got a date field in your example, not a datetime field, so the following is my preferred method:
您的示例中有一个日期字段，而不是日期时间字段，因此以下是我的首选方法：
# Mysql
select * from
your_table
where date_field = (select max(date_field) from your_table)
// Laravel
YourModel:::query()->whereRaw('date_field = (select max(date_field) from your_table)')->get();
- Note the above wont work as expected for datetime fields because the rows will have different timestamps, it'll likely just return the single latest row.
- 请注意，上述对于日期时间字段不会按预期工作，因为行将具有不同的时间戳，它可能只会返回单个最新行。
回答by Saad
You might also want to orderBymessage_idas well
您可能还需要orderBymessage_id以及
$messages = Message::where('to', Auth::id())
->groupBy('from')
->orderBy('created_at', 'DESC')
->orderBy('message_id', 'DESC')
->paginate(10);
