skip to Main Content

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


  1. You have done excellent job setting up relationships in each Models.

    For your question , you can achieve this using nested eager loading.

    $student = Student::query()
       ->where('id', auth()->id())
       ->with('grades.courses')
       ->first();
    
    $grades = $student->grades->paginate(5);
    
    return view('dashboard.index', compact('student', 'grades'));
    

    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.

    $user = $request->user(); // or auth()->user()
    $grades = Grade::query()
       ->where('student_id', $user->id)
       ->with('courses')
       ->paginate(5);
    
    return view('dashboard.index', compact('user', 'grades'));
    

    This still will run only 3 queries.

    note: $request->user() is the same as auth()->user()
    and in the query i use auth()->id() to get the logged in user Id

    Login or Signup to reply.
  2. Your pagination is not hitting a queryBuilder but a Collection 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 the courses relation of Student::class in your controller.

    To improve everything, you can do it like this

    public function index(Request $request)
    {
        $student = $request->user();
    
        return view('dashboard.index', [
            'user' => $student,
            'grades' => $student->grades()->with('courses')->paginate(10),
        ]);
    }
    

    Calling $student->grades() returns a QueryBuilder instead of calling $student->grades which returns a Collection

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search