Convert row SQL query to Laravel Eloquent Query

Question

I need this query to be Laravel eloquent, and also, how can I join the above tables in one single eloquent relational query? I have tried many examples to solve this query if any ideas on how to solve this please describe me in response.enter link description here

Answer 1

Here was the source code which builds on its own with some assumptions. Because it was difficult to assume all related tables and it's content.

php artisan make:model Course

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

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

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

Update User Model

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 Subscription

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 Client

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']);

Here is the final Query which you write in CourseController controller.

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

Hope this will help you.