在 Laravel 4 中检索特定日期范围的数据记录的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24519775/
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
Query for retrieving data records for a specific date range in Laravel 4
提问by Vishal Nair
I have a table having record_dateand corresponding amountfield.I was trying to retrieve total amount grouping by the months of the date .
I am new to laravel and in normal php i would have used the following mysql query -->SELECT MONTH(record_date),YEAR(record_date),SUM(amount)FROM table_nameWHERE record_date > DATE_SUB(now(), INTERVAL 6 MONTH)GROUP BY YEAR(record_date), MONTH(record_date);
This simply returns the total amount collected for last 6 months each grouped by the month and the year .
04 | 2014 | 200.00
06 | 2014 | 500.00
08 | 2014 | 100.00
I have read Laravel eloquentdocumentation but cannot find something equivalent to the query i am trying to run . I dont know if this is right way but the nearest equivalent to the above query, i came across is
我有一个表，有record_date和相应的amount字段。我试图检索按日期的月份分组的总金额。
我是 laravel 的新手，在普通的 php 中，我会使用以下 mysql 查询 -->SELECT MONTH(record_date),YEAR(record_date),SUM(amount)FROM table_nameWHERE record_date > DATE_SUB(now(), INTERVAL 6 MONTH)GROUP BY YEAR(record_date), MONTH(record_date);
这只是返回过去 6 个月收集的总金额，每个月按月和年分组。
04 | 2014 | 200.00
06 | 2014 | 500.00
08 | 2014 | 100.00
我已阅读Laravel eloquent文档，但找不到与我正在尝试运行的查询等效的内容。我不知道这是否正确，但与上述查询最接近的等价物，我遇到的是
$data = SOME_MODEL::whereBetween('record_date', array(01, 12))->get();
But not sure if its even close to what i am trying to do.
Problem is the documentation does not gives date functions like now() and other which we normally always use in the mysql queries.
但不确定它是否甚至接近我想要做的。
问题是文档没有提供像 now() 和其他我们通常总是在 mysql 查询中使用的日期函数。
采纳答案by The Alpha
You need a rawquery and you may try this (Not tested but should work):
你需要一个raw查询，你可以试试这个（未经测试但应该可以工作）：
$data = DB::table('table_name')
->select(DB::raw('MONTH(record_date) as m, YEAR(record_date) as y, SUM(amount) as t'))
->whereRaw('record_date > DATE_SUB(now(), INTERVAL 6 MONTH)')
->groupBy(DB::raw('YEAR(record_date), MONTH(created_at)'))
->get();
回答by Jake Wilson
I would avoid using Eloquent for a more complex query such as this. Just stick with the query builder:
我会避免将 Eloquent 用于更复杂的查询，例如这样。坚持使用查询构建器：
$data = DB::table('table_name')
->select(DB::raw('MONTH(record_date)'),DB::raw('YEAR(record_date)'),DB::raw('SUM(amount)'))
->where('record_date', '>', DB::raw('DATE_SUB(now(), INTERVAL 6 MONTH)'))
->groupBy(DB::raw('YEAR(record_date)'))
->groupBy(DB::raw('MONTH(record_date)'))
->get();
Unfortunately, since you are using so many MySQL functions, you need to add in a lot of DB::rawmethods. But it should work. This is untested but it should be very close.
不幸的是，由于您使用了如此多的 MySQL 函数，因此您需要添加很多DB::raw方法。但它应该工作。这是未经测试的，但应该非常接近。
