Laravel MySql join table

Question

I'm working on a laravel 5 application. I have issue getting results from two database table. here is what i have:

table A: 'courses' 

id   |    Course
————————————————
1    |    Math
2    |    History
3    |    Geography
4    |    Computer

and Table B

user_id | classroom_id | course 
1       | 5            | 3
1       | 5            | 4
1       | 6            | 2

I returned the table A on a for each loop but I would like to check what courses the user_id 1 has to return true or false on every column on the for-each loop. Something like this:

Returned item for user_id 1:

id   |    course     |  status
____________________________
1    |    Math       | false
2    |    History    | true
3    |    Geography  | true
4    |    Computer   | true

This is I have:

$AllList = DB::table('users')
            ->join('courses', 'users.id', '=', 'courses.parent_id')
            ->join('classroom', 'users.id', '=', 'classroom.user_id')->where('classroom_id', '=', 5)       
            ->get();

Any help appreciated.

Answer 1

Just replace join with leftJoin:

$AllList = DB::table('courses')
            ->select('courses.id','course.name')
            ->leftJoin('users', 'users.id', '=', 'courses.parent_id')
            ->join('classroom', 'users.id', '=', 'classroom.user_id')->where('classroom_id', '=', 5)       
            ->get();

Course field will be NULL thus empty string if there is no match

Answer 2

the below lines of code will help you...

    $getCourse = DB::table('courses')->get(['id','course']);

    $getCourse = collect($getCourse)->map(function($x){ return (array) $x; })->toArray();

    foreach ($getCourse as $key => $value)
    {
        $flag = DB::table('classroom')
                    ->where('user_id',1)
                    ->where('course',$value['id'])
                    ->pluck('id');
        if($flag)
        {
            $getCourse[$key]['status'] = true;
        }
        else
        {
            $getCourse[$key]['status'] = false;
        }
    }

    dd($getCourse);