phpmysqllaravellaravel-query-builderwhere-in

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.

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 of student_course table 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. Here is 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? How can I run raw query as second parameter? If not, how can I filter by course_id just in one query like above and not joining to any table?


Solution

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