CODEWITHSUNDEEP
laravellaravel-5
jsmitter3
jsmitter3

Reputation: 443

Laravel : Group users by month and output month name

I am querying users created in Laravel by month, here is my code

  $devlist = DB::table("users")
                ->select("id" ,DB::raw("(COUNT(*)) as month"))
                ->orderBy('created_at')
                ->groupBy(DB::raw("MONTH(created_at)"))
                ->get();

This is giving me this

[{"id":1,"month":3},{"id":4,"month":4}]

How can I output the month as well instead of just the word month? So my expected result would be

[{"id":1,"january":3},{"id":4,"febuary":4}]

Upvotes: 3

Views: 1561

Answers (4)

Mahedi Hasan
Mahedi Hasan

Reputation: 1068

Simply use this

\App\Model::selectRaw('monthname(date) as month, sum(total_amount) as total_sale')
        ->groupBy('month')
        ->orderByRaw('min(date) desc')
        ->get();

Upvotes: 0

Ahsan
Ahsan

Reputation: 1359

It's difficult to answer this without seeing the database, but I hope this will help:

$devlist = DB::table("users")
   ->select(DB::raw('EXTRACT(MONTH FROM created_at) AS month, COUNT(id) as id'))
   ->orderBy('created_at')
   ->groupBy(DB::raw('month'))
   ->get();

Upvotes: 4

Rohit Bachkar
Rohit Bachkar

Reputation: 21

$statement = Purchase::selectRaw('monthname(purchase_date) month')
            ->groupBy('month')
            ->orderByRaw('min(purchase_date) asc')
        ->get();
        echo "<pre>";
        print_r($statement);
        die;

Upvotes: 2

Chris
Chris

Reputation: 58142

Hard to tell without seeing your DB structure, but something like this would be fine. As noted, the output you are showing doesn't line up with the groupBy clause, but assuming the groupBy is correct, this will restructure the way you need:

$devlist = DB::table("users")
                ->select("id" ,DB::raw("(COUNT(*)) as month"))
                ->orderBy('created_at')
                ->groupBy(DB::raw("MONTH(created_at)"))
                ->get()
                ->map(function($monthItems, $month) {
                    return $monthItems->map(function($item) use ($month) {
                      return [
                          'id' => $item->id,
                          $month => $item->month,
                      ];
                    });
                });

Upvotes: 0

Related Questions