Quite often we need to list the table of data not just entry by entry, but grouped by date. As examples, sports games by date, registered users by date and more. Let’s see how to do it quickly in Laravel.

Example of demo that we will created:

As you can see, it is a list of users grouped by their register date or to be more precise, users.created_at column.

First, I’ve seeded 50 fake users, by specifying their created_at some time in the past, randomly over 30 days and here is database/factories/UserFactory.php:

$factory->define(App\User::class, function (Faker $faker) {
    return [
        'name' => $faker->name,
        'email' => $faker->unique()->safeEmail,
        'email_verified_at' => now(),
        'password' => '$2y$10$TKh8H1.PfQx37YgCzwiKb.KjNyWgaHb9cbcoQgdIVFlYg7B77UdFm', // secret
        'remember_token' => str_random(10),
        'created_at' => now()->subDays(rand(1,30)),
    ];
});

Next, seeded 50 users in database/seeds/DatabaseSeeder.php:

class DatabaseSeeder extends Seeder
{
    public function run()
    {
        factory(\App\User::class, 50)->create();
    }
}

Now, let’s get to today’s topic on how to group the data by date. Here’s the controller code:

use App\User;

class UserController extends Controller
{

    public function index()
    {
        $users = User::orderBy('created_at')->get()->groupBy(function($item) {
            return $item->created_at->format('Y-m-d');
        });

        return view('users', compact('users'));
    }

}

A few notices:

  • Function groupBy() is called AFTER the get(), it is a Collection function and not Eloquent. If you do groupBy() before get(), it wouldn’t work, cause Eloquent groupBy() does not accept callback parameter.
  • I’m doing group by formatted column $item->created_at->format(‘Y-m-d’), it will work only on created_at/updated_at column that are automatically transformed to Carbon objects. If you want to group by other date/time fields, you should use Carbon, like Carbon::createFromFormat(‘Y-m-d H:i:s’, $item->register_time)

Now, here’s the code for the table in our resources/views/users.blade.php file:

<table class="table">
    @foreach ($users as $day => $users_list)
        <tr>
            <th colspan="3"
                style="background-color: #F7F7F7">{{ $day }}: {{ $users_list->count() }} users</th>
        </tr>
        @foreach ($users_list as $user)
            <tr>
                <td>{{ $user->name }}</td>
                <td>{{ $user->email }}</td>
                <td>{{ $user->created_at }}</td>
            </tr>
        @endforeach
    @endforeach
</table>

As you can see, we can still call $users_list->count() on the result too.

In short, you can do groupBy() with callback function, after getting all query results.

Credit to: https://bit.ly/34i6Cog