[SOLVED] Convert row SQL query to Laravel Eloquent Query

Convert row SQL query to Laravel Eloquent Query

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

Solution

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.