laravel关联查询
1.创建表:
-- 创建学生表 CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '姓名', `age` tinyint(3) NOT NULL DEFAULT '0' COMMENT '年龄', `sex` tinyint(3) NOT NULL DEFAULT '0' COMMENT '性别,0:未知,1:男,2:女', `created_at` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间', `updated_at` int(11) NOT NULL DEFAULT '0' COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生表'; INSERT INTO `student`(`id`, `name`, `age`, `sex`, `created_at`, `updated_at`) VALUES (3, '小王', 18, 2, 1641469262, 1641469262); INSERT INTO `student`(`id`, `name`, `age`, `sex`, `created_at`, `updated_at`) VALUES (10, '小张', 18, 1, 1641469804, 1641529381); INSERT INTO `student`(`id`, `name`, `age`, `sex`, `created_at`, `updated_at`) VALUES (11, '小李', 19, 1, 1641469904, 1641529381); INSERT INTO `student`(`id`, `name`, `age`, `sex`, `created_at`, `updated_at`) VALUES (14, '小朱', 19, 1, 1641525262, 1641529381); INSERT INTO `student`(`id`, `name`, `age`, `sex`, `created_at`, `updated_at`) VALUES (15, '小杜', 18, 1, 1641526322, 1641529381); INSERT INTO `student`(`id`, `name`, `age`, `sex`, `created_at`, `updated_at`) VALUES (17, '小梁', 17, 1, 1641526483, 1641529381); INSERT INTO `student`(`id`, `name`, `age`, `sex`, `created_at`, `updated_at`) VALUES (18, '小吴', 18, 1, 1641527008, 1641529381); INSERT INTO `student`(`id`, `name`, `age`, `sex`, `created_at`, `updated_at`) VALUES (19, '小周', 17, 1, 1641527008, 1641529381); -- 创建学生信息表 CREATE TABLE `student_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL DEFAULT '0' COMMENT '学生id', `email` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '邮箱', `address` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '地址', `created_at` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间', `updated_at` int(11) NOT NULL DEFAULT '0' COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生信息表'; INSERT INTO `student_info`(`id`, `student_id`, `email`, `address`, `created_at`, `updated_at`) VALUES (1, 3, 'test3@test.com', '山东省济南市历下区', 1642404313, 1642404313); INSERT INTO `student_info`(`id`, `student_id`, `email`, `address`, `created_at`, `updated_at`) VALUES (2, 10, 'test10@test.com', '天津市南开区', 1642404313, 1642404313); INSERT INTO `student_info`(`id`, `student_id`, `email`, `address`, `created_at`, `updated_at`) VALUES (3, 11, 'test11@test.com', '河北省石家庄市裕华区', 1642404313, 1642404313); INSERT INTO `student_info`(`id`, `student_id`, `email`, `address`, `created_at`, `updated_at`) VALUES (4, 14, 'test14@test.com', '河北省廊坊市广阳区', 1642404313, 1642404313); INSERT INTO `student_info`(`id`, `student_id`, `email`, `address`, `created_at`, `updated_at`) VALUES (5, 15, 'test15@test.com', '山东省青岛市南市区', 1642404313, 1642404313); INSERT INTO `student_info`(`id`, `student_id`, `email`, `address`, `created_at`, `updated_at`) VALUES (6, 17, 'test17@test.com', '河南省郑州市中原区', 1642404313, 1642404313); INSERT INTO `student_info`(`id`, `student_id`, `email`, `address`, `created_at`, `updated_at`) VALUES (7, 18, 'test18@test.com', '河南省南阳市卧龙区', 1642404313, 1642404313); -- 创建考试结果表 CREATE TABLE `exam` ( `id` int(11) NOT NULL AUTO_INCREMENT, `exam_name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '科目', `fraction` decimal(5,2) NOT NULL DEFAULT '0.00' COMMENT '考试成绩', `student_id` int(11) NOT NULL DEFAULT '0' COMMENT '学生id', `created_at` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间', `updated_at` int(11) NOT NULL DEFAULT '0' COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='考试结果表'; INSERT INTO `exam`(`id`, `exam_name`, `fraction`, `student_id`, `created_at`, `updated_at`) VALUES (1, '数学', 67.00, 3, 1642402349, 1642402349); INSERT INTO `exam`(`id`, `exam_name`, `fraction`, `student_id`, `created_at`, `updated_at`) VALUES (2, '数学', 100.00, 10, 1642402349, 1642402349); INSERT INTO `exam`(`id`, `exam_name`, `fraction`, `student_id`, `created_at`, `updated_at`) VALUES (3, '数学', 98.00, 11, 1642402349, 1642402349); INSERT INTO `exam`(`id`, `exam_name`, `fraction`, `student_id`, `created_at`, `updated_at`) VALUES (4, '数学', 63.00, 14, 1642402349, 1642402349); INSERT INTO `exam`(`id`, `exam_name`, `fraction`, `student_id`, `created_at`, `updated_at`) VALUES (5, '数学', 96.00, 15, 1642402349, 1642402349); INSERT INTO `exam`(`id`, `exam_name`, `fraction`, `student_id`, `created_at`, `updated_at`) VALUES (6, '数学', 78.00, 17, 1642402349, 1642402349); INSERT INTO `exam`(`id`, `exam_name`, `fraction`, `student_id`, `created_at`, `updated_at`) VALUES (7, '数学', 77.00, 18, 1642402349, 1642402349); -- 创建创建角色表 CREATE TABLE `roles` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '角色名称', `created_at` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间', `updated_at` int(11) NOT NULL DEFAULT '0' COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色表'; INSERT INTO `roles`(`id`, `role_name`, `created_at`, `updated_at`) VALUES (1, '班长', 1642411155, 1642411155); INSERT INTO `roles`(`id`, `role_name`, `created_at`, `updated_at`) VALUES (2, '副班长', 1642411155, 1642411155); INSERT INTO `roles`(`id`, `role_name`, `created_at`, `updated_at`) VALUES (3, '班委', 1642411155, 1642411155); INSERT INTO `roles`(`id`, `role_name`, `created_at`, `updated_at`) VALUES (4, '课代表', 1642411155, 1642411155); -- 创建学生角色表 CREATE TABLE `student_role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_id` int(11) NOT NULL DEFAULT '0' COMMENT '角色id', `student_id` int(11) NOT NULL DEFAULT '0' COMMENT '学生id', `created_at` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间', `updated_at` int(11) NOT NULL DEFAULT '0' COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生角色表'; INSERT INTO `student_role`(`id`, `role_id`, `student_id`, `created_at`, `updated_at`) VALUES (1, 1, 3, 1642413366, 1642413366); INSERT INTO `student_role`(`id`, `role_id`, `student_id`, `created_at`, `updated_at`) VALUES (2, 2, 10, 1642413366, 1642413366); INSERT INTO `student_role`(`id`, `role_id`, `student_id`, `created_at`, `updated_at`) VALUES (3, 3, 11, 1642413366, 1642413366); INSERT INTO `student_role`(`id`, `role_id`, `student_id`, `created_at`, `updated_at`) VALUES (4, 4, 14, 1642413366, 1642413366); INSERT INTO `student_role`(`id`, `role_id`, `student_id`, `created_at`, `updated_at`) VALUES (5, 4, 15, 1642413366, 1642413366); INSERT INTO `student_role`(`id`, `role_id`, `student_id`, `created_at`, `updated_at`) VALUES (6, 4, 17, 1642413366, 1642413366); INSERT INTO `student_role`(`id`, `role_id`, `student_id`, `created_at`, `updated_at`) VALUES (7, 4, 18, 1642413366, 1642413366); INSERT INTO `student_role`(`id`, `role_id`, `student_id`, `created_at`, `updated_at`) VALUES (8, 4, 3, 1642413366, 1642413366);
2.一对一关联查询:
//student表一对一正向关联student_info表:在student表model中添加关联方法: public function studentInfo() { return $this->hasOne(StudentInfo::class, 'student_id', 'id'); } //查询数据: $list = Student::find(20)->studentInfo; //student_info表一对一反向关联student表:在student_info表model中添加关联方法: public function student() { return $this->belongsTo(Student::class, 'student_id', 'id'); } //查询数据: $list = StudentInfo::find(3)->student;
3.一对多关联查询:
//student表一对多正向关联exam表:在student表model中添加关联方法: public function exam() { return $this->hasMany(Exam::class, 'student_id', 'id'); } //查询数据: $list = Student::find(3)->exam; //一对多,不带查询条件 $list = Student::find(3)->exam()->where('fraction', '>=', 80)->get(); //一对多,带查询条件 //student表一对多反向关联exam表:在exam表model中添加关联方法: public function student() { return $this->belongsTo(Student::class, 'student_id', 'id'); } //查询数据: $list = Exam::find(3)->student;
4.多对多关联查询:
//student表多对多关联roles表(通过student_role中间表进行关联,student_id和role_id字段为student_role表中的字段):在student表model中添加关联方法: public function roles() { return $this->belongsToMany(Roles::class, 'student_role', 'student_id', 'role_id'); } //查询数据: $list = Student::find(3)->roles; //可以将SQL语句打印出来查看下是否正确 $sql = Student::find(3)->roles()->toSql(); //增加查询条件: $list = Student::find(3)->roles()->where(function($query) { $query->where('role_name', '课代表'); })->get(); //has:判断student表与roles表是否存在关联数据。参数一为model名,当前查询信息为student与role表存在关联数据小于1的,若只写roles参数,则默认为大于等于1。 $list = Student::has('roles', '>', 1)->get(); //whereHas:使用闭包组合查询条件 $list = Student::whereHas('roles', function ($query) { $query->where('role_name', '班长'); })->get(); //doesntHave,has的反向操作,判断student表与roles表不存在关联的数据。 $list = Student::doesntHave('roles')->get(); //withCount:统计student表每条数据与roles表关联数据数量 $list = Student::withCount('roles')->get(); //doesntHave使用时设置别名,增加查询条件 $list = Student::withCount(['roles as role_count'=>function($query) { $query->where('roles.id', 1); }])->get();
参考文档:
https://learnku.com/docs/laravel/8.x/eloquent-relationships/9407
【版权申明】未经博主同意,谢绝转载!(请尊重原创,博主保留追究权) https://www.cnblogs.com/facetwitter/p/15815769.html