Laravel-从一个表中获取不存在的记录，并附加一个where子句 [英] Laravel - Get records from one table that doesn't exist in another with a where clause attached

问题描述

我有以下SQL表(在MySQL中):

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          |
+----+------------+-------------+

我如何从students table和enrollments table两者中招募当前学年未注册的学生，或者本学年在enrollments table中没有任何记录的学生.

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.

现在，我可以使用以下查询从enrollments table中没有注册详细信息的学生获取students table表格:

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

根据此查询上方表中的数据，将返回学生Peter - 03.

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

但是如何获得当前学年没有入学详细信息的学生?

这是我确定本学年的方式:

This is how I determine the current academic year:

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

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

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.

推荐答案

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

让我们提供一些细节:

1- whereNotExists子句将仅返回子查询中没有任何行的学生.

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

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

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

希望这会有所帮助

这篇关于Laravel-从一个表中获取不存在的记录，并附加一个where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！