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

Question

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.

Answer 1

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

Let's give some details:

1- whereNotExists clause will return only students that doesn't have any row in the sub query.

2- the sub query select students that exists in enrollments table and their academics_id is 10

Hope this helps