I have a small Laravel app that is basically a few tables as follows and I’m having a problem with Laravel Eloquent generating more SQL queries than it needs. I am basically trying to display all ‘grades’ for a student that is logged in but to keep the queries to a minimum.
Here is my controller
public function index(Request $request)
{
$student = $request->user();
return view('dashboard.index', [
'user' => $student,
'grades' => $student->grades->paginate(5)
]);
}
Here are my table structures
courses
select id, name from courses;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | English Literature |
| 2 | Stats |
| 3 | Biology |
+----+--------------------+
grades
select id, student_id, course_id, score, letter_grade from grades where student_id = 1;
+----+------------+-----------+--------+--------------+
| id | student_id | course_id | score | letter_grade |
+----+------------+-----------+--------+--------------+
| 1 | 1 | 1 | 90.00 | A |
| 2 | 1 | 2 | 50.00 | D |
| 3 | 1 | 3 | 100.00 | A |
+----+------------+-----------+--------+--------------+
course_student
select * from course_student;
+-----------+------------+
| course_id | student_id |
+-----------+------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+-----------+------------+
students
select id, email from students limit 1;
+----+---------------------------+
| id | email |
+----+---------------------------+
| 1 | [email protected] |
+----+---------------------------+
My Laravel relations in the models look like the following:
Student.php
public function grades(): IlluminateDatabaseEloquentRelationsHasMany
{
return $this->hasMany(Grade::class);
}
public function courses(): IlluminateDatabaseEloquentRelationsBelongsToMany
{
return $this->belongsToMany(Course::class);
}
Grade.php
class Grade extends Model
{
use HasFactory;
public function student(): IlluminateDatabaseEloquentRelationsBelongsTo
{
return $this->belongsTo(Student::class);
}
public function course(): IlluminateDatabaseEloquentRelationsBelongsTo
{
return $this->belongsTo(Course::class);
}
}
Course.php
class Course extends Model
{
use HasFactory;
public function department(): IlluminateDatabaseEloquentRelationsHasOne
{
return $this->hasOne(Department::class, 'id', 'department_id');
}
public function students(): IlluminateDatabaseEloquentRelationsBelongsToMany
{
return $this->belongsToMany(Student::class);
}
public function teachers(): IlluminateDatabaseEloquentRelationsBelongsToMany
{
return $this->belongsToMany(Teacher::class);
}
}
DashboardController.php
public function index(Request $request)
{
$student = $request->user();
return view('dashboard.index', [
'user' => $student,
'grades' => $student->load('grades', 'courses')->grades->paginate(10)
]);
}
dashboard/index.blade.php view
<div class="py-2">
<div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
<div class="bg-white dark:bg-gray-800 sm:rounded-lg">
{{ $grades->links() }}
<table>
<tr>
<td>Course</td>
<td>Grade</td>
<td>Score</td>
<td>Date</td>
</tr>
@foreach ($grades as $key => $grade)
<tr>
<td>{{ $grade->course->name }}</td>
<td>{{ $grade->letter_grade }}</td>
<td>{{ $grade->score }}</td>
<td>{{ $grade->created_at }}</td>
</tr>
@endforeach
</table>
</div>
</div>
</div>
Using Laravel Debugbar I can see all of the queries it has generated – I don’t have any errors but the SELECT * from courses could be optimised to use an IN() rather than an entire SELECT * from courses by primary key each time, how do I optimise my controller/model logic
select * from `students` where `id` = 1 and `students`.`deleted_at` is null limit 1
select * from `grades` where `grades`.`student_id` = 1 and `grades`.`student_id` is not null
select * from `courses` where `courses`.`id` = 1 limit 1
select * from `courses` where `courses`.`id` = 2 limit 1
select * from `courses` where `courses`.`id` = 3 limit 1
select * from `courses` where `courses`.`id` = 4 limit 1
select * from `courses` where `courses`.`id` = 5 limit 1
Expected output to use fewer queries to the courses table & to NOT lazy load queries
2
Answers
You have done excellent job setting up relationships in each Models.
For your question , you can achieve this using nested eager loading.
As documented here : https://laravel.com/docs/9.x/eloquent-relationships#nested-eager-loading
Now your laravel should run only 3 queries on table students, grades and courses.
And if you want only to get grades with pagination, try run this query.
This still will run only 3 queries.
note:
$request->user()
is the same asauth()->user()
and in the query i use
auth()->id()
to get the logged in user IdYour pagination is not hitting a
queryBuilder
but aCollection
instead: Doing$student->grades
already retrieves all the grades of the student, that’s why the query has no limit in it.You are using the
courses
relation of Grade::class in your blade yet you loaded thecourses
relation of Student::class in your controller.To improve everything, you can do it like this
Calling
$student->grades()
returns a QueryBuilder instead of calling$student->grades
which returns a Collection