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.

first you have to create a belongs to relation,then when you query "courses" just check is there any relation with table b then print rather true or false.Please show here what you tried,that we can help you through code or logic — Imtiaz Pabel
– Imtiaz Pabel, Commented Sep 20, 2016 at 8:35
select course from A inner join B on A.id=B.couse where B.user_id=1; — Sanzeeb Aryal
– Sanzeeb Aryal, Commented Sep 20, 2016 at 8:44

ka_lin · Accepted Answer · 2016-09-20 11:53:40Z

2

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

edited Sep 20, 2016 at 11:53

answered Sep 20, 2016 at 8:59

ka_lin

9,4226 gold badges39 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Martian.titan Over a year ago

Thanks for your answer. So how to limit the returned columns?because I have all columns on both tables.

ka_lin Over a year ago

Just add ->select([column names as array]); like I did above

Martian.titan Over a year ago

Cool, Thanks. I just voted up you answer and not selecting as the accepted answer to get more answer form other people. Thanks again

Martian.titan Over a year ago

the reason why didn't choose as the answer because I still get Null for all the Data: [{"id":null,"name":null},{"id":null,"name":null},{"id":null,"name":null},{"id":null,"name":null}]

ka_lin Over a year ago

Oh sorry, I replaced 'courses with users because there are users with no courses, that is why you are getting empty values

Ketan Trentiya · Accepted Answer · 2016-09-20 10:57:07Z

1

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

answered Sep 20, 2016 at 10:57

Ketan Trentiya

594 bronze badges

Collectives™ on Stack Overflow

Laravel MySql join table

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related