一对多模型关联

1.前言

前面小节介绍了如何在课程列表中使用一对一模型关联出教师信息，本小节介绍如何在学生课程关联列表中一对多关联出学生选课信息，一对多模型关联主要针对的是查询两个表有一对多的关系，而连表查询就可能带来性能问题， ThinkPHP中提供了 with()方法用于这种连表查询的预加载，在一对多模型关联的时候，框架底层会用过主驱动表的 id 集去另外一张表中 IN 查询一次获取全部数据并且能自动对应，这样的操作就减少了开发者书写很多循环和处理数据的代码了。

2.添加测试数据

这里为了演示方便，需要向之前新建好的 学生表(student)、学生课程关联表(student_course) 添加数据，添加学生表数据的 SQL 如下：

如下图所示：

添加学生课程关联表数据 SQL 语句如下：

如下图所示：

3.定义路由

这里复用上小节的控制器，只需要定义一个学生课表关列表的路由：

//学生课程表关联列表接口
Route::get('course-students','app\controller\Study\CourseController@courseStudnetList');

如下图所示：

4.方法定义

    /**
     * 学生课表信息关联表
     * @return \think\response\Json
     */
    public function courseStudnetList(){
        //每页条数
        $size = (int)$this->request->param('size', 15);

        $courseStudents = StudentModel::order('created_at DESC')
            ->with('course_students')
            ->paginate($size);

        return json($courseStudents);
    }

如下图所示：

5.设置一对多模型关联方法

在 StudentModel 中可以定义 courseStudents 方法：

    /**
     * 一对多模型关联
     * @return \think\model\relation\HasMany
     */
    public function courseStudents(){
        return $this->hasMany(StudentCourseModel::class,"student_id","id");
    }

如下图所示：

6.请求模型关联之后的数据

下面使用 postman 请求接口数据如下：

{
    "total": 14,
    "per_page": 10,
    "current_page": 1,
    "last_page": 2,
    "data": [
        {
            "id": 1,
            "name": "赵雷",
            "age": 24,
            "id_number": "42011720100506XXXX",
            "created_at": 1603617951,
            "update_at": 0,
            "status": 1,
            "course_students": [
                {
                    "id": 1,
                    "student_id": 1,
                    "course_id": 1,
                    "created_at": 1603617951
                },
                {
                    "id": 2,
                    "student_id": 1,
                    "course_id": 2,
                    "created_at": 1603617951
                },
                {
                    "id": 3,
                    "student_id": 1,
                    "course_id": 2,
                    "created_at": 1603617951
                }
            ],
            "created_at_text": "2020-10-25 17:25"
        },
        {
            "id": 2,
            "name": "孙空",
            "age": 23,
            "id_number": "42011720110606XXXX",
            "created_at": 1603617951,
            "update_at": 0,
            "status": 1,
            "course_students": [
                {
                    "id": 4,
                    "student_id": 2,
                    "course_id": 3,
                    "created_at": 1603617951
                },
                {
                    "id": 5,
                    "student_id": 2,
                    "course_id": 4,
                    "created_at": 1603617951
                },
                {
                    "id": 6,
                    "student_id": 2,
                    "course_id": 6,
                    "created_at": 1603617951
                }
            ],
            "created_at_text": "2020-10-25 17:25"
        },
        {
            "id": 3,
            "name": "钱学",
            "age": 18,
            "id_number": "42011720120306XXXX",
            "created_at": 1603617951,
            "update_at": 0,
            "status": 1,
            "course_students": [
                {
                    "id": 7,
                    "student_id": 3,
                    "course_id": 4,
                    "created_at": 1603617951
                },
                {
                    "id": 8,
                    "student_id": 3,
                    "course_id": 1,
                    "created_at": 1603617951
                }
            ],
            "created_at_text": "2020-10-25 17:25"
        },
        {
            "id": 4,
            "name": "王五",
            "age": 25,
            "id_number": "42011720030506XXXX",
            "created_at": 1603617951,
            "update_at": 0,
            "status": 1,
            "course_students": [
                {
                    "id": 9,
                    "student_id": 4,
                    "course_id": 1,
                    "created_at": 1603617951
                },
                {
                    "id": 10,
                    "student_id": 4,
                    "course_id": 3,
                    "created_at": 1603617951
                }
            ],
            "created_at_text": "2020-10-25 17:25"
        },
        {
            "id": 5,
            "name": "张红",
            "age": 19,
            "id_number": "42011720050506XXXX",
            "created_at": 1603617951,
            "update_at": 0,
            "status": 1,
            "course_students": [
                {
                    "id": 11,
                    "student_id": 5,
                    "course_id": 5,
                    "created_at": 1603617951
                },
                {
                    "id": 12,
                    "student_id": 5,
                    "course_id": 6,
                    "created_at": 1603617951
                }
            ],
            "created_at_text": "2020-10-25 17:25"
        },
        {
            "id": 6,
            "name": "吴晓明",
            "age": 21,
            "id_number": "42011720040506XXXX",
            "created_at": 1603617951,
            "update_at": 0,
            "status": 1,
            "course_students": [
                {
                    "id": 13,
                    "student_id": 6,
                    "course_id": 4,
                    "created_at": 1603617951
                },
                {
                    "id": 14,
                    "student_id": 6,
                    "course_id": 2,
                    "created_at": 1603617951
                }
            ],
            "created_at_text": "2020-10-25 17:25"
        },
        {
            "id": 7,
            "name": "李珍",
            "age": 25,
            "id_number": "42011720060206XXXX",
            "created_at": 1603617951,
            "update_at": 0,
            "status": 1,
            "course_students": [
                {
                    "id": 15,
                    "student_id": 7,
                    "course_id": 3,
                    "created_at": 1603617951
                },
                {
                    "id": 16,
                    "student_id": 7,
                    "course_id": 4,
                    "created_at": 1603617951
                }
            ],
            "created_at_text": "2020-10-25 17:25"
        },
        {
            "id": 8,
            "name": "猪刚",
            "age": 22,
            "id_number": "42011720070806XXXX",
            "created_at": 1603617951,
            "update_at": 0,
            "status": 1,
            "course_students": [],
            "created_at_text": "2020-10-25 17:25"
        },
        {
            "id": 9,
            "name": "李亮",
            "age": 26,
            "id_number": "42011720080906XXXX",
            "created_at": 1603617951,
            "update_at": 0,
            "status": 1,
            "course_students": [],
            "created_at_text": "2020-10-25 17:25"
        },
        {
            "id": 10,
            "name": "周康",
            "age": 28,
            "id_number": "42011720000706XXXX",
            "created_at": 1603617951,
            "update_at": 0,
            "status": 1,
            "course_students": [],
            "created_at_text": "2020-10-25 17:25"
        }
    ]
}

如下图所示：

7.小结

本小节主要介绍了一对多模型关联，其中使用 with 预加载底层实现是 IN 查询一次性查出来的，所以无需担心存在循环查询的问题，另外如需要在上述结果中继续关联出课程信息可以如下使用 with 闭包查询：

    /**
     * 学生课表信息关联表
     * @return \think\response\Json
     */
    public function courseStudnetList(){
        //每页条数
        $size = (int)$this->request->param('size', 15);

        $courseStudents = StudentModel::order('created_at DESC')
            ->with(['course_students' => function($query){
                $query->with('course');
            }])
            ->paginate($size);

        return json($courseStudents);
    }

其中需要在 CourseStudentModel 模型中新建 course 一对一关联课程信息。

Tips： 代码仓库：https://gitee.com/love-for-poetry/tp6