这里是源代码，它是在一些假设的基础上构建的。因为很难假设所有相关的表和它的内容。
php artisan make：model课程

class Course extends Model
{
    protected $table = 'courses';

    public function subscriptions()
    {
        return $this->belongsToMany(Subscription::class);
    }

    public function lessons()
    {
        return $this->hasMany(Lesson::class);
    }
}

更新用户模型

class User extends Model
{
    protected $table = 'users';

    public function client()
    {
        return $this->belongsTo(Client::class);
    }

    public function lessons()
    {
        return $this->belongsToMany(Lesson::class)->withPivot('result');
    }
}

php artisan make：model订阅

class Subscription extends Model
{
    protected $table = 'subscriptions';

    public function client()
    {
        return $this->belongsTo(Client::class);
    }

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

class Client extends Model
{
    protected $table = 'clients';

    public function subscriptions()
    {
        return $this->hasMany(Subscription::class);
    }

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

php artisan make：model客户端

class Lesson extends Model
{
    protected $table = 'lessons';

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

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

web.php

Route::get('/view-course/{client_id}', ['as' => 'course.view', 'uses' => 'CourseController@viewCourseByClient']);

这是您在CourseController控制器中编写的最后一个查询。

public function viewCourseByClient($client_id){
  $completed_courses = Course::select('courses.name as course_name', 'courses.description as course_description')
    ->addSelect(DB::raw('SUM(course_completed) as user_completed_count'))
    ->addSelect(DB::raw('COUNT(DISTINCT tmp.user_id) as total_user_count'))
    ->join('subscription_courses', 'courses.id', '=', 'subscription_courses.course_id')
    ->join('subscriptions', 'subscription_courses.subscription_id', '=', 'subscriptions.id')
    ->join('clients', 'subscriptions.client_id', '=', 'clients.id')
    ->join('users', 'clients.id', '=', 'users.client_id')
    ->join('course_lessons', 'courses.id', '=', 'course_lessons.course_id')
    ->join('lessons', 'course_lessons.lesson_id', '=', 'lessons.id')
    ->leftJoin('user_lessons', function ($join) use ($client_id) {
        $join->on('lessons.id', '=', 'user_lessons.lesson_id')
            ->on('users.id', '=', 'user_lessons.user_id')
            ->where('user_lessons.result', '=', 'completed')
            ->where('clients.id', '=', $client_id);
    })
    ->groupBy('courses.id', 'users.id')
    ->selectSub(function ($query) {
        $query->selectRaw('CASE WHEN COUNT(DISTINCT CASE WHEN user_lessons.result = \'completed\' THEN course_lessons.lesson_id END) = COUNT(DISTINCT course_lessons.lesson_id) THEN 1 ELSE 0 END as course_completed')
            ->from('courses')
            ->join('subscription_courses', 'courses.id', '=', 'subscription_courses.course_id')
            ->join('subscriptions', 'subscription_courses.subscription_id', '=', 'subscriptions.id')
            ->join('clients', 'subscriptions.client_id', '=', 'clients.id')
            ->join('users', 'clients.id', '=', 'users.client_id');

  return view('course.completed_courses', compact($completed_courses));
}

希望这对你有帮助。