来自数据库的最新数据 [英] Distinct most recent data from database
问题描述
我正在将数据存储在数据库中.存储的数据看起来像这样
I am storing data in my database. The data being stored looks like this
id | upload_month | created_at
-----------------------------------------
1 | January | 2017-01-30 13:22:39
-----------------------------------------
2 | Febuary | 2017-01-30 13:23:42
-----------------------------------------
3 | January | 2017-01-30 13:25:33
在我的控制器内,我尝试检索不同的upload_month,但获取每个文件的最新插入版本.此刻我正在尝试
Within my Controller I am trying to retrieve the distinct upload_month, but get the latest inserted version for each. At the moment I am trying
$uploadedFile = UploadedFile::groupBy('upload_month')->orderBy('created_at', 'desc')->get();
问题在于,这将返回以下内容
The problem is that this is returning the following
id | upload_month | created_at
-----------------------------------------
1 | January | 2017-01-30 13:22:39
-----------------------------------------
2 | Febuary | 2017-01-30 13:23:42
-----------------------------------------
因此对于一月份的记录,它提供的是较旧的版本.如果将其更改为->orderBy('created_at', 'asc')
,它将返回相同的记录,但Febuary是第一行.
So for the January record it is giving the older version. If I change it to ->orderBy('created_at', 'asc')
it returns the same records but Febuary being the first row.
从本质上讲,我所追求的是这个
In essense, what I am after is this
id | upload_month | created_at
-----------------------------------------
1 | January | 2017-01-30 13:25:33
-----------------------------------------
2 | Febuary | 2017-01-30 13:23:42
-----------------------------------------
我怎么能做到这一点?
谢谢
推荐答案
您应该GROUP BY
您要选择的所有字段,而不仅仅是一个.本文介绍了该问题:
You should GROUP BY
all fields you want to select, no only one. This article explain the issue: https://www.psce.com/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/
在这种情况下,正确的SQL查询将是:
The proper SQL query in this case would be:
SELECT id, upload_month, created_at
FROM uplodaded_file
JOIN (SELECT upload_month, MAX(created_at) created_at
FROM uplodaded_file
GROUP BY upload_month) months
ON upload_month = months.upload_month
AND created_at = months.created_at
雄辩的版本有点棘手.在这种情况下,最好使用原始查询.
The eloquent version of this is a little bit tricky. It will be better to use a raw query in this case.
这篇关于来自数据库的最新数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!