CODEWITHSUNDEEP
phpmysqllaravellaravel-query-builderwhere-in
Wai Yan Hein
Wai Yan Hein

Reputation: 14771

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?

Upvotes: 2

Views: 8063

Answers (4)

Mostafa Asadi
Mostafa Asadi

Reputation: 397

for who one use query builder:

$data = DB::table('student')
->whereIn('id',DB::table('student_course')
->where('course_id','=',$courseId)
->pluck('student_id')
->toArray())
->get();

Upvotes: 0

Andrea
Andrea

Reputation: 86

If you want pass through model, you can use:

$res = Student::whereIn('id',StudentCourse::where('course_id',$course_id)->pluck('student_id')->toArray())->get();

Upvotes: 1

kapilpatwa93
kapilpatwa93

Reputation: 4411

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

Upvotes: 7

aceraven777
aceraven777

Reputation: 4556

You can do this:

DB::table('student')
    ->whereRaw("id IN (SELECT `student_id` FROM `student_course` WHERE `course_id`= ?)", [$course_id])
    ->get();

Upvotes: 0

Related Questions