使用Joins在laravel视图中显示Json数据

Question

I'm trying to retrieve data from database to laravel blade view with join associate table.

in my case I have two tables called interesting_courses and courses. here some student can have many interesting courses. Therefore courses_id stored as json array in database as follows.

["1","11","15","16"] 

but I need to join the courses table to get the associate course name as follows.

["Hospitality","Business Management","Auto Mobile","Health Care"]

Below is my controller

$intresting_courses = DB::table('intresting_courses')
                            ->join('courses','courses.id','=','intresting_courses.courses_id')
                            ->where('intresting_courses.youth_id',$id)
                            ->first();

How can I join the tables.

Answer 1

There is no way you can join it in query natively.

The solution is to write two queries

select * from intresting_courses where youth_id = ? limit 1

select * from courses where id in (1, 2, 3, 4, 5, ...)

that way you can get courses that student intresting.

Here the laravel code for thoses queries.

$intresting_courses = DB::table('intresting_courses')
                        ->where('intresting_courses.youth_id', $id)
                        ->first();

$courses = DB::table('courses')
             ->whereIn('id', json_decode($intresting_courses->courses_id))
             ->get();