一对多模型关联
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