How to Calculate the Average of a Column Using Eloquent

Written by Johan van den Broek in Database Published 2024-02-03Last Updated 2024-02-03

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

To calculate the average of a column in 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!

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' => 'Inception']);
$movie2 = Movie::create(['title' => 'The Shawshank Redemption']);
$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]);

After this was executed your database should look like this:

The Contents of Our Database After Inserting the Sample Data

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:

The Browser Showing our Eloquent Calculated Average Ratings in a Table

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:

Showing our Eloquent Calculated Average Ratings in a Prettier Styled Table

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 Laracoding</p>
</footer>

</body>
</html>

Conclusion

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.

Now, using this knowledge, feel free to enhance your Laravel projects with calculated averages based on your own data. Happy coding!

References

This entry is part 1 of 4 in the series Aggregating With Laravel Eloquent

  1. How to Calculate the Average of a Column Using Eloquent
  2. How to Calculate the Sum of Multiple Columns Using Eloquent
  3. How to Calculate the Sum of a Relation Column in Eloquent
  4. How to Get the Max Value of a Column Using Eloquent

Johan van den Broek

Johan is the creator of laracoding.com. As a child, he began tinkering with various programming languages, many of which have been long forgotten today. Currently, he works exclusively with PHP and Laravel, and his passion for programming remains to this day.

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Posts