将行SQL查询转换为Laravel Eloquent查询

Question

我需要这个查询是Laravel雄辩的，而且，我如何将上面的表连接到一个雄辩的关系查询中？我已经尝试了很多例子来解决这个问题。如果有关于如何解决这个问题的想法，请在回复中描述我。在这里输入链接描述

Answer 1

这是源代码，它在自己的基础上进行了一些假设。因为很难假设所有相关的表及其内容。

php手工制作：模型课程

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手工制作：模型订阅

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手工制作：模型客户端

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));
}

希望这对你有帮助。