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:
<?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:
<?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:
<?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:
<?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:
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
:
<?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:
<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:
<?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:
<!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
- Eloquent Relationships: Other Aggregate Functions (Laravel Documentation)
- Database Migrations (Laravel Documentation)
- Bootstrap 5: Tables (Bootstrap Documentation)
- Bootstrap 5: Icons (Bootstrap Icons Official Page)
This entry is part 1 of 4 in the series Aggregating With Laravel Eloquent
- How to Calculate the Average of a Column Using Eloquent
- How to Calculate the Sum of Multiple Columns Using Eloquent
- How to Calculate the Sum of a Relation Column in Eloquent
- How to Get the Max Value of a Column Using Eloquent