CODEWITHSUNDEEP
phpmysqlsqldatabaselaravel
Nathan Siafa
Nathan Siafa

Reputation: 741

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.

Upvotes: 11

Views: 21019

Answers (3)

YouneL
YouneL

Reputation: 8351

$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

Upvotes: 17

Amarnasan
Amarnasan

Reputation: 15529

Let's do this the most eloquential way possible:

First off, you need to have this in your Student.php model file

public function academics()
{
    return $this->belongsToMany('App\Academic', 'enrollments', 'student_id', 'academic_id'); 
}

and this in your Academic.php model file

public function students()
{
    return $this->belongsToMany('App\Student', 'enrollments', 'academic_id','student_id'); 
}

now you can get what you want this way:

$students = \App\Student::whereDoesntHave('academics')
->orWhereHas('academics',function($q) use ($academic){
  $q->where('id',$academic->id)->count();
},'=',0)->get();

Upvotes: 2

Amarnasan
Amarnasan

Reputation: 15529

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

Upvotes: 2

Related Questions