如何在Laravel中进行选择后对结果进行分组

Question

I have a controller that returns me expiration_date and status of a ticket, referring an user_id.

After I get all tickets that an user has registered, the controller will show when expire and the status. Something like this:

0   
expiration_date "2018-03-03 20:38:32"
status  "expired"

1   
expiration_date "2018-03-03 20:38:32"
status  "expired"

2   
expiration_date "2018-03-03 20:38:32"
status  "expired"

3
expiration_date "2018-05-03 09:02:06"
status  "expired"

4
expiration_date "2019-05-03 09:02:06"
status  "available"

5   
expiration_date "2019-05-03 10:00:20"
status  "available"

My problem here is that I need to group the data that has the same expiration_date and status. So, I would group the data 0, 1, 2 and the data 4 and 5 in an unique item, counting the number of items. Something like this:

Count: 3
expiration_date "2018-03-03 20:38:32"
status  "expired"

Count: 1
expiration_date "2018-05-03 09:02:06"
status  "expired"

Count: 2
expiration_date "2019-05-03 10:00:20"
status  "available"

So, how do I do that? I'm using Laravel with mysql and here it is my Controller.

ControllerPHP

<?php

namespace App\Http\Controllers;

use DB;
use Illuminate\Http\Request;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class UsersCreditsController extends Controller 
{

    public function getTicketsStatusByUserId($userId)
    {   

        $users = DB::select("
            SELECT COUNT(user_id)
             FROM users_credits 
             WHERE user_id = $userId
        ");

        foreach ($users as $user) {
          $item = DB::select("
              SELECT expiration_date, status
              FROM users_credits 
              WHERE user_id = $userId
              ORDER BY expiration_date ASC
          ");
          return $item;
        }
    }
}

Answer 1

So, I remade my code using the eloquent that was indicated to me.

<?php

namespace App\Http\Controllers;

use DB;
use App\Http\Requests;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class UsersCreditsController extends Controller 
{
    public function getTicketsStatusByUserId($userId)
    {   
        $item = DB::table('users_credits')
                    ->select('expiration_date as Vencimento', 'status as Status', DB::raw('count(*) as Quantidade'))
                    ->where(function ($query) use ($userId)
                    {
                        $query->where('user_id', '=', $userId);
                        $query->whereNotIn('status', ['used']);
                    })
                    ->groupBy('expiration_date', 'status')
                    ->get();
        return $item;
    }
}

The code is working as intended, if anyone need to learn how to use this.