CODEWITHSUNDEEP
phpmysqlsqllaravel
Ahmad Badpey
Ahmad Badpey

Reputation: 6612

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.

Upvotes: 1

Views: 80

Answers (2)

Ahmad Badpey
Ahmad Badpey

Reputation: 6612

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

Upvotes: 1

KmasterYC
KmasterYC

Reputation: 2354

I think this might be not the best way. But it is easy to understand and maintainable

$courses = $user->courses->map(function($cource){
            $all_lessions = $cource->pivot->count();
            $done_lessions = $cource->pivot->where(passed,'<>',0)->count();
            $percent = $done_lessions * 100 / $all_lessions;
            return $cource->push(['percent'=>$percent]);
        });

Now you can access through

foreach ($courses as $cource){
    $cource->percent;
    $cource->title; 
    //...
}

Upvotes: 1

Related Questions