Laravel - 从一个表中获取记录，该记录在另一个表中不存在，并附有 where 子句

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

提问by Nathan Siafa

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

我有以下 SQL 表（在 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 tableand the enrollments tablewho are not enrolled for the current academic year or of whom no records exists in the enrollments tablefor the current academic year.

如何获得来自学生students table和enrollments table谁没有报名参加本学年或谁没有记录在存在的enrollments table当前学年。

For now I can get the student form the students tablewho has no enrollment details in the enrollments tablewith this query:

现在，我可以通过以下查询获取students table没有注册详细信息的学生enrollments table：

$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.

根据上表中的数据，此查询将返回 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 tableso that I can query out students who have no enrollment records for the current academic year. Will appreciate your earnest answers and suggestions.

使用现有查询如何加入，academics table以便我可以查询当前学年没有注册记录的学生。将感谢您的认真答复和建议。

回答by YouneL

$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 detail:

让我们给出一些细节：

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

1-whereNotExists子句将只返回在子查询中没有任何行的学生。

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

2- 子查询选择存在于注册表中的学生，他们的 Academics_id 为 10

Hope this helps

希望这可以帮助

回答by Amarnasan

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

回答by Amarnasan

Let's do this the most eloquential way possible:

让我们以最有说服力的方式做到这一点：

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

首先，你需要在你的 Student.php 模型文件中有这个

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

and this in your Academic.php model file

这在你的 Academic.php 模型文件中

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