查询Laravel 4中特定日期范围的数据记录 [英] Query for retrieving data records for a specific date range in Laravel 4

问题描述

我有一个表格有 record_date 和相应的金额 field.I我试图通过几个月的日期。

我是新来的laravel和正常php我会使用以下mysql查询 - >

SELECT MONTH（record_date），YEAR （record_date），SUM（amount）
FROM table_name
WHERE record_date> DATE_SUB（now（），INTERVAL 6 MONTH）
GROUP BY YEAR（record_date），MONTH（record_date）;
< br>

这简单地返回过去6个月收集的总金额，每个月按年份分组。
04 | 2014 | 200.00
06 | 2014 | 500.00
08 | 2014 | 100.00

我已阅读 Laravel雄辩的文档，但找不到与我相似的查询试图跑。我不知道这是否是正确的方式，但最近相当于上面的查询，我发现是

I have a table having record_date and corresponding amount field.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_name
WHERE 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 eloquent documentation 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

$data = SOME_MODEL::whereBetween('record_date', array(01, 12))->get();

但不知道是否甚至接近我想要做的事情。

问题是文档没有给出现在（）和其他通常在mysql查询中使用的日期函数。

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.

推荐答案

您需要一个原始查询，您可以尝试这个（未测试但应该工作）：

You need a raw query and you may try this (Not tested but should work):

$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();

这篇关于查询Laravel 4中特定日期范围的数据记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！