How to solved N+1 Problem

I have a Course Table and User table, and those tables relations on a pivot_table. Now I am checking which course user is enrolling in the course model like this but it is creating an n+1 problem. how to solve this.

check enroll

'enrolled' => $this->users()->where('user_id', auth()->id())->exists(),

Query

 'courses' => CourseHomePageResource::collection(
                Course::with(['topics', 'users'])
                    ->where('status', 'published')
                    ->latest()
                    ->take(8)
                    ->get()
            ),

User Model

 public function courses()
    {
        return $this->belongsToMany(Course::class);
    }

Course Model

 public function users()
    {
        return $this->belongsToMany(User::class);
    }

it's show

select exists(select * from `users` inner join `course_user` on `users`.`id` = `course_user`.`user_id` where `course_user`.`course_id` = 4 and `user_id` = 1) as `exists`

select exists(select * from `users` inner join `course_user` on `users`.`id` = `course_user`.`user_id` where `course_user`.`course_id` = 3 and `user_id` = 1) as `exists`

select exists(select * from `users` inner join `course_user` on `users`.`id` = `course_user`.`user_id` where `course_user`.`course_id` = 2 and `user_id` = 1) as `exists`

select exists(select * from `users` inner join `course_user` on `users`.`id` = `course_user`.`user_id` where `course_user`.`course_id` = 1 and `user_id` = 1) as `exists`
polash
polash
0
3
188
alex
alex
Moderator
Solution

Thanks for including that query log, super helpful. It looks like you're calling exists within the CourseHomePageResource which would cause a loop, since exists will always perform a query and not use eager loaded data.

I'd recommend using a collection for this instead. Something like:

'enrolled' => $this->users->contains(auth()->id()),

I haven't tested this code, but you should be good!

By accessing the collection only, you're searching only within the collection and not performing an additional query.

Just a warning though, this can get slower the more users you have enrolled in the course, but should be fine for now! Check back in if you have any more issues.

polash
polash

Thank you so much @alex, now it's working perfectly

alex
alex
Moderator

Glad to hear that Polash!