Laravel’s Eloquent offers a variety of features to retrieve, store and update data. Interestingly it can also aggregate and calculate things like sums, maximum or averages. In this post we learn how to aggregate data with Eloquent along with a calculated average

How to Calculate the Average of a Column Using Eloquent we can do 2 things:

// Method 1: Get the average rating of all movies combined (single model)
$avgStar = Rating::avg('rating');

// Method 2: Use `withAvg` to get each movie and its average rating (using relationship)
$movies = Movie::select()
    ->withAvg('ratings', 'rating')
    ->get();

In this post we will create an application where users browse a movie database and views their average ratings. For this, we will use the withAvg method to calculate the average, and read the resulting value from the ratings_avg_rating attribute (following Laravel’s naming convention).

We will render the results using a Blade view with a user-friendly table.

Let’s get started! How to Calculate the Average of a Column Using Eloquent

Step 1: Create a Laravel Project

Start by creating a new Laravel project by running:

composer create-project laravel/laravel movie-rating-app
cd movie-rating-app

Step 2: Generate Migrations

Generate migrations for the ‘movies’ and ‘ratings’ tables by executing the following artisan commands:

php artisan make:migration create_movies_table --create=movies
php artisan make:migration create_ratings_table --create=ratings

Step 3: Migration Code

Now let’s edit the generated migration code and add the code schemas for the ‘movies’ and ‘ratings’ tables.

For ‘movies’ use:

database/migrations/2024_01_30_203609_create_movies_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('movies', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('movies');
    }
};

For ‘ratings’ use:

database/migrations/2024_01_30_203610_create_ratings_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('ratings', function (Blueprint $table) {
            $table->id();
            $table->foreignId('movie_id')->constrained();
            $table->integer('rating');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('ratings');
    }
};

Step 4: Run the Migrations

Now lets create our ‘movies’ and ‘ratings’ tables by running:

php artisan migrate

Step 5: Create Models

Now, we’ll use Artisan to generate the models for ‘Movie’ and ‘Rating’ by running:

php artisan make:model Movie
php artisan make:model Rating

Step 6: Model Code

Now we’ll edit the Models and add the code below, to define the relationships and fillable columns.

For Movie, use:

app/Models/Movie.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Movie extends Model
{
    protected $fillable = [
        'title',
    ];

    public function ratings()
    {
        return $this->hasMany(Rating::class);
    }
}

For Rating, use:

app/Models/Rating.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Rating extends Model
{
    protected $fillable = [
        'rating',
    ];

    public function movie()
    {
        return $this->belongsTo(Movie::class);
    }
}

Step 7: Insert Test Data

In this step, we will use Laravel Tinker to insert some sample data. Start by running the following command:

php artisan tinker

Once the Tinker shell is open, you can paste the code below. This code creates three movie entries and assigns a star rating to each, simulating user votes:

use App\Models\Movie;
use App\Models\Rating;

$movie1 = Movie::create(['title' => 'The Avenger']);
$movie2 = Movie::create(['title' => 'Man of Steel']);
$movie3 = Movie::create(['title' => 'The Marvels']);

$movie1->ratings()->create(['rating' => 4]);
$movie1->ratings()->create(['rating' => 5]);

$movie2->ratings()->create(['rating' => 5]);
$movie2->ratings()->create(['rating' => 5]);

$movie3->ratings()->create(['rating' => 2]);
$movie3->ratings()->create(['rating' => 3]);

Step 8: Create a Controller

Now we’ll generate the MovieController class by running:

php artisan make:controller MovieController

Step 9: Add Controller Code

In the generated controller, we’ll be using Eloquent to calculate the average rating of the movies and pass the data to a view. To achieve this add the code below to MovieController.php:

app/Http/Controllers/MovieController.php

<?php

namespace App\Http\Controllers;

use App\Models\Movie;

class MovieController extends Controller
{
    public function index()
    {
        $movies = Movie::select()
            ->withAvg('ratings', 'rating')
            ->get();

        return view('movies.index', compact('movies'));
    }
}

Step 10: Create a View

Now let’s create a Blade view which will display a list of the movies together with their average ratings. Create a “resources/views/movies” folder and add a file “index.blade.php” and copy & paste the following code into it:

resources/views/movies/index.blade.php

<h1>Movies and Average Ratings</h1>
<table class="table">
    <thead>
    <tr>
        <th>Title</th>
        <th>Average Rating</th>
    </tr>
    </thead>
    <tbody>
    @foreach($movies as $movie)
        <tr>
            <td>{{ $movie->title }}</td>
            <td>
                <!-- Print average as a rounded number with a precision of 1 -->
                {{ number_format($movie->ratings_avg_rating, 1) }}
            </td>
        </tr>
    @endforeach
    </tbody>
</table>

Step 11: Add a Route

In this step we’ll add a route to routes/web.php and define its controller action using the following code:

routes/web.php

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\MovieController;

Route::get('/movies', [MovieController::class, 'index']);

Step 12: Test the Application

Now it’s time to run the application and test it!

First, run the application by executing the following Artisan command:

php artisan serve

Afterwards visit http://127.0.0.1:8000/movies in your browser to see the table of movies with their average ratings.

The result should show the following table with average ratings correctly calculated:

That’s it! You’ve successfully created an application that calculates averages of ratings using Eloquent and displays them in a table.

If you wish, you can take this application one step further and make it look nicer. In the next step we’ll be adding bootstrap 5, some custom CSS and star icons to show the ratings.

Step 13: Improve the Styling (Optional)

For simplicity we made a the table in step 12 as basic as possible. In this step we’ll improve our view to make it look like this:

To achieve the styling in this image open resources/views/movies/index.blade.php and replace the your blade code with the code below and refresh your page:

resources/views/movies/index.blade.php

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <!-- Include Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.min.css" rel="stylesheet">

    <style>
        /* Custom CSS for alternating row colors */
        .table tbody tr:nth-child(odd) {
            background-color: #f8f9fa; /* Alternating color for odd rows */
        }

        /* Custom CSS for star rating */
        .star-rating {
            display: flex;
            align-items: center;
        }

        .star-rating i {
            color: #ffd700; /* Set the color of stars to yellow */
            font-size: 20px;
            margin-right: 2px;
        }

        /* Position average number */
        .average-rating {
            font-size: 16px;
            color: #000;
            font-weight: bold;
            margin-left: 5px;
        }
    </style>
</head>
<body>

<div class="container">
    <header class="mt-3 text-center">
        <h1>Movies and Average Ratings</h1>
    </header>
    <table class="table table-striped table-bordered">
        <thead>
        <tr>
            <th>Title</th>
            <th>Average Rating</th>
        </tr>
        </thead>
        <tbody>
        @foreach($movies as $movie)
            <tr>
                <td>{{ $movie->title }}</td>
                <td>
                    <div class="star-rating">
                        @for ($i = 1; $i <= 5; $i++)
                            @if ($i <= $movie->ratings_avg_rating)
                                <!-- Bootstrap Icons star icon filled -->
                                <i class="bi bi-star-fill"></i>
                            @else
                                <!-- Bootstrap Icons star icon outline -->
                                <i class="bi bi-star"></i>
                            @endif
                        @endfor
                        <span class="average-rating">
                            <!-- Print average as a rounded number with a precision of 1 -->
                            {{ number_format($movie->ratings_avg_rating, 1) }}
                        </span>
                    </div>
                </td>
            </tr>
        @endforeach
        </tbody>
    </table>
</div>

<footer class="mt-5 text-center">
    <p>Created with ♥ by DevScriptSchool.Com</p>
</footer>

</body>
</html>

Output:

Congratulations! You’ve successfully built an application using Laravel’s Eloquent to calculate the average rating for each movie in a database. This tutorial demonstrated that Eloquent can go beyond basic CRUD operations and can aggregate records and perform calculations on them.

By following the steps in this guide, you’ve set-up a fresh project, it’s migrations, models and relationships, added test data, and implemented a controller + view to display average ratings in a user-friendly table.

You Can Learn How to Add Toastr Notification in Laravel 11?