Laravel - Get records from one table that doesn't exist in another with a where clause attached

I've got the following SQL tables (in MySQL):

students
+-----+------------+
| id  | first_name | 
+-----+------------+
| 01  | John       |
+-----+------------+
| 02  | Jane       | 
+-----+------------+
| 03  | Peter      | 
+-----+------------+

academics
+-----+-----------+----------+------+
| id  | year_start| year_end |status|
+-----+-----------+----------+------+
| 10  | 2016      | 2017     |1     |
+-----+-----------+----------+------+
| 20  | 2017      | 2018     |0     |
+-----+-----------+----------+------+

enrollments
+----+------------+-------------+
| id | student_id | academic_id |
+----+------------+-------------+
| 1  | 01         | 10          |
+----+------------+-------------+
| 2  | 02         | 20          |
+----+------------+-------------+
| 3  | 01         | 20          |
+----+------------+-------------+

How do I get students from both the students table and the enrollments table who are not enrolled for the current academic year or of whom no records exists in the enrollments table for the current academic year.

For now I can get the student form the students table who has no enrollment details in the enrollments table with this query:

$students = \DB::table('students')
        ->select(
            'students.id',
            'first_name'
        )
        ->leftJoin('enrollments','enrollments.student_id','=','students.id')
        ->whereNull('enrollments.student_id')
        ->get();

Based on the data in the table above this query will return student Peter - 03.

BUT HOW CAN I GET STUDENTS WHO HAVE NO ENROLLMENT DETAILS FOR THE CURRENT ACADEMIC YEAR?

This is how I determine the current academic year:

$current_academic = Academic::where('status', 1)->first();

With the existing query how do I join on the academics table so that I can query out students who have no enrollment records for the current academic year. Will appreciate your earnest answers and suggestions.

xmarks

8 years ago

Level 8

Have you tried this?

$students = Student::leftJoin('enrollments','enrollments.student_id','=','students.id')
    ->leftJoin('academics', 'enrollments.academic_id', '=', 'academics.id')
    ->whereNull('enrollments.student_id')
    ->where('academics.status', '=', 1)
    ->get();

2 likes

Tangaye

8 years ago

Best Answer

Level 1

Sorry, that won't work. Here is what work for me:

    $current_academic = Academic::where('status', 1)->first();

$students = \DB::table('students')
    ->select(
        'students.id',
        'first_name'
    )
    ->whereNotExists( function ($query) use ($current_academic) {
        $query->select(DB::raw(1))
        ->from('enrollments')
        ->whereRaw('students.id = enrollments.student_id')
        ->where('enrollments.academic_id', '=', $current_academic->id);
    })->get();

Thanks for the reply anyway.

Please or to participate in this conversation.