CODEWITHSUNDEEP
phpmysqllaravellaravel-5eloquent
Dev.W
Dev.W

Reputation: 2370

Laravel Carbon Group by Month

Can anyone see what I'm doing wrong?

I'm trying to output all the months but group them so they are unique.

$months = NewsItem::select(DB::raw('MONTH("created_at") as month'))->groupBy('month')->get();
return $months;

I'm getting the following back

{"month":null}

In my database I have five news articles all created_at 05/01/2017 so it's right that I only get one response but I'm not getting the number of the month back?

Upvotes: 7

Views: 22775

Answers (6)

Mrunali Khandekar
Mrunali Khandekar

Reputation: 65

$month = NewsItem::select(
DB::raw('DATE_FORMAT(created_at, "%M") as month'))
->groupBy('month')
->get();

Upvotes: 0

Fathima Fasna
Fathima Fasna

Reputation: 91

To get earnings on monthly basis you can try the below code:-

$getMonthlyReport = OrderMaster::select(DB::raw("sum(order_total_amt) as        earnings,date_format(order_date, '%Y-%m') as YearMonth"))
    ->groupBy('order_date')
    ->get();

Upvotes: 0

shamaseen
shamaseen

Reputation: 2478

You can do it this way:

NewsItem::get(["*",\DB::raw('MONTH(created_at) as month')])->groupBy('month');

Upvotes: 1

Dayachand Patel
Dayachand Patel

Reputation: 497

You can simply using groupby and mysql MONTH.

$months = NewsItem::groupby(\DB::raw('MONTH(created_at) as month'))->get();

Upvotes: 1

Robert
Robert

Reputation: 5963

Why do you need the group by clause in your usecase?

You are not fetching any additional data for which a group by is required, you just want to have a list of distinct months, so use distinct.

$months = NewsItem::selectRaw("MONTH(created_at) as month")->distinct()->get();

Also looking at the solution provided by Alexey, you'll need to fetch the entire dataset from the DB, which is highly inefficient looking at what you are trying to do. A distinct() query would be much faster than a select * and group the results in PHP.

Edit:

Little sidenote here, the reason you get null returned as value is because you use a string in the MONTH() function instead of the actual field.

Upvotes: 3

Alexey Mezenin
Alexey Mezenin

Reputation: 163758

You can use groupBy() method with closure:

 $months = NewsItem::groupBy(function($d) {
     return Carbon::parse($d->created_at)->format('m');
 })->get();

Or get data first and then use groupBy() on the Eloquent collection:

 $months = NewsItem::get()->groupBy(function($d) {
     return Carbon::parse($d->created_at)->format('m');
 });

Upvotes: 22

Related Questions