CODEWITHSUNDEEP
phpsqllaravel-5eloquent
TheShun
TheShun

Reputation: 1218

Laravel Eloquent, group by month/year

i'm trying to group my data by month and year.

$data ->select(DB::raw('count(id) as `data`'),DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
           ->groupby('year','month')
           ->get();

The output is :

{
"data": 19215,
"year": 2016,
"month": 10
},

if i group only by month, i don't know from which year belong this month, my expected output is :

{
"clicks": 19215,
"month": 11-2016,
},
{
"clicks": 11215,
"month": 12-2016,
},

i want to do it in sql, not in php.

Upvotes: 29

Views: 107064

Answers (7)

wittich
wittich

Reputation: 2311

The answer by @Amit Gupta is a good, but it isn't working >= MySQL 5.7/MariaDB 10.2 and calls the error:

Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

To solve that issue, you can use eg. MIN() on the nonaggregated column:

->select(
    DB::raw("
    count(id) as data, 
    MIN(DATE_FORMAT(created_at, '%m-%Y')) as new_date, 
    YEAR(created_at) year, 
    MONTH(created_at) month
    ")
)
->groupBy('year', 'month')
->get();

Upvotes: 2

ctf0
ctf0

Reputation: 7579

for latest version of mysql, the accepted answer will give error, so instead try

$data
    ->selectRaw("
        count(id) AS data, 
        DATE_FORMAT(created_at, '%Y-%m') AS new_date, 
        YEAR(created_at) AS year, 
        MONTH(created_at) AS month
    ")
    ->groupBy('new_date')
    ->get();

ref: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

Upvotes: 8

Brane
Brane

Reputation: 3339

Note: This is solution based on eloquent, and yes, it not the best approach, but as the question is, this is using eloquent. If you want faster way, you can do it with raw query.

If you want to group your results for example by year, you can use closure where you will parse your datetime to desired format (year or month).

Here is example of the code:

$res= ModelName::where('someColumn','test')
      ->get()
      ->groupBy(function($val) {
      return Carbon::parse($val->created_at)->format('Y');
});

Upvotes: 29

ppegu
ppegu

Reputation: 492

=>this might help someone in 2019 

$date = "2019-06-16";

return date('m', strtotime($date)); //this return you 06(edited)

so you can do foreach by this method , so you don't need to do the all sql things..

Upvotes: 1

umar Aslam
umar Aslam

Reputation: 39

$data ->select(DB::raw('count(id) as `data`'),DB::raw('YEAR(created_at) year'),DB::raw('MONTH(created_at) month'))
       ->groupBy(DB::raw('YEAR(created_at)'), DB::raw('MONTH(created_at)'))
       ->get();

Upvotes: 2

Otim Fredrick
Otim Fredrick

Reputation: 361

$result = ModelName::selectRaw('year(created_at) year, monthname(created_at) month, count(*) data')
                ->groupBy('year', 'month')
                ->orderBy('year', 'desc')
                ->get();

Upvotes: 20

Amit Gupta
Amit Gupta

Reputation: 17658

You can try as:

->select(DB::raw('count(id) as `data`'), DB::raw("DATE_FORMAT(created_at, '%m-%Y') new_date"),  DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
->groupby('year','month')
->get();

Upvotes: 58

Related Questions