A.B.Developer A.B.Developer - 3 months ago 12
SQL Question

use laravel advanced where clauses to run a query

Suppose I have a

Course
Model like this :

class Course extends Model
{
public function users ()
{
return $this->belongsToMany('App\User', 'course_user', 'course_id', 'user_id');

}

public function lessons ()
{
return $this->hasMany('App\Lesson', 'course_id', 'course_id');
}
}


Course
fields are :

course_id
title


Each Course can have multiple lessons.

Lesson
Model is like :

class Lesson extends Model
{
public function course ()
{
return $this->belongsTo('App\Course', 'course_id', 'course_id');
}
public function users ()
{
return $this->belongsToMany('App\User', 'lesson_user', 'lesson_id', 'user_id');
}
}


And it's fields are:

lesson_id
title
course_id


As you see there is a OneToMany relation between
Course
and
Lesson
and a ManyToMany relation between
User
and
Course
.

User
And
Course
Pivot table named ~course_user` have these fields :

course_id
user_id


In the other hand there is a ManyToMany relation between
User
and
Lesson
. pivot table for those named
lesson_user
and have these fields :

lesson_id
user_id
passed


passed
field show status of a user in a lesson. if it was 0 ,means user has not passed it yet otherwise he passed it.

User
Model is like :

class User extends Model
{
public function lessons()
{
return $this->belongsToMany('App\Lesson', 'lesson_user', 'user_id', 'lesson_id')
}
public function courses ()
{
return $this->belongsToMany('App\Course', 'course_user', 'user_id', 'course_id');
}
}


Now I want to get user courses and calculate percent of passed lessons in each Course via best way, for example nested where clauses.

Answer

With inspiration from @KmasterYC answer I wrote bellow codes and all things work:

$userCourses =
            $currentUser->courses()
                ->take(3)
                ->get();


        $userCourses->map(function ($course) use ($currentUser) {

            $allLessonsCount = $course->lessons->count();

            $courseLessonID = $course->lessons->lists('lesson_id')->toArray();

            $userLessonsCount = $currentUser->lessons()
                ->where('passed', '=', true)
                ->whereIn('lesson_user.lesson_id', $courseLessonID)
                ->count();
            $percent          = round($userLessonsCount * 100 / $allLessonsCount);

            $course['percent'] = $percent;
        });