Wai Yan Hein Wai Yan Hein - 7 days ago 7
MySQL Question

Using Laravel query builder method whereIn() with sub query

I am developing a Web Application using Laravel 5.3. I am now having an issue with Laravel query builder method, whereIn. Please see my scenario below.

I have table like this.

student - id, name, dob
course - id, code
student_course - student_id, course_id


What I want to do is I want to get students searching by course_id by course without joining any table. In manual query, I do like this.

SELECT * FROM `student` WHERE `student`.`id` IN (SELECT `student_id` FROM `student_course` WHRE `course_id`= ?)


So now when I try to convert it into Laravel query builder, I have a problem. Please have a look at my code.

DB::table('student')->whereIn('id',[ "Here I want to run sub query" ])->get();


As you can see in the second parameter of whereIn(), I get problem. In this scenario, do I need to run raw query? Please how can I run raw query as second parameter please? If not, how can I filter by course_id just in one query like above and not joining to any table please? Please help me.

Answer

refer this

 $courseId = 1;

 $data = DB::table('student')->whereIn('id',function($query) use (courseId){
    $query->select('student_id')
            ->from('student_course')->where('course_id', '=',$courseId);
})->get();