Query for retrieving data records for a specific d

Question

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

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.

Answer 1

I would avoid using Eloquent for a more complex query such as this. Just stick with the query builder:

$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::raw methods. But it should work. This is untested but it should be very close.

Answer 2

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