连表查询与模型关联

最新推荐文章于 2023-07-19 19:19:02 发布

呀哈

最新推荐文章于 2023-07-19 19:19:02 发布

阅读量877

点赞数

文章标签：数据库 thinkphp php

本文链接：https://blog.csdn.net/weixin_43321108/article/details/121831999

版权

本文介绍了MySQL中的连表查询，用于获取商品品牌及其分类信息。同时，探讨了在PHP框架中如何使用模型关联进行数据操作，包括一对一、一对多关联查询，以及多对多关联的实现。模型关联简化了复杂的SQL查询，提高了查询效率，同时也使得代码更易于理解和维护。

摘要由CSDN通过智能技术生成

连表查询

在使用数据库查询语句时，单表的查询有时候不能满足项目的业务要求，在项目开发过程中，有很多需求都是涉及到多表的连接查询，总结一下mysql中的多表关联查询

需求：查询所有商品品牌信息及其所属分类名称

连表查询一个品牌以及分类名称
SELECT t1.*,t2.cate_name FROM `pyg_brand` t1 left join pyg_category t2 on t1.cate_id = t2.id where t1.id = 1;
连表查询所有品牌以及对应的分类名称
SELECT t1.*,t2.cate_name FROM `pyg_brand` t1 left join pyg_category t2 on t1.cate_id = t2.id;

对应框架中的代码：

// 连表查询一个品牌以及分类名称
$info = \app\common\model\Brand::alias('t1')
            ->join('pyg_category t2', 't1.cate_id=t2.id', 'left')
            ->field('t1.*, t2.cate_name')
            ->where('t1.id', 1)
            ->find();
// 连表查询所有品牌以及对应的分类名称
$list = \app\common\model\Brand::alias('t1')
                ->join('pyg_category t2', 't1.cate_id=t2.id', 'left')
                ->field('t1.*, t2.cate_name')
                ->select();

转化为数组形式的结果

$info = ['id' => 1, 'name' => '华为',..., 'cate_name'=>'手机']；

$list = [
 	['id' => 1, 'name' => '华为',..., 'cate_name'=>'手机']；
  	['id' => 2, 'name' => '小米',..., 'cate_name'=>'手机']；
];

为什么要是用模型关联

通过模型关联操作把数据表的关联关系对象化，解决了大部分常用的关联场景，封装的关联操作比起常规的数据库联表操作更加智能和高效，并且直观

避免在模型内部使用复杂的join查询和视图查询

模型关联的方式一对一关联

举例：新增管理员档案表pyg_profile，保存每个管理员的详细信息(身份证号、银行卡号)。

CREATE TABLE `pyg_profile` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `idnum` varchar(30) DEFAULT NULL COMMENT '身份证号',
  `card` varchar(255) DEFAULT NULL COMMENT '银行卡号',
  `create_time` int(11) DEFAULT NULL,
  `update_time` int(11) DEFAULT NULL,
  `delete_time` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

添加测试数据

INSERT INTO `pyg`.`pyg_profile` (`id`, `uid`, `idnum`, `card`, `create_time`, `update_time`, `delete_time`) VALUES ('1', '1', '232332198008083321', '421656421254789', '1520408547', '1520408547', NULL);
INSERT INTO `pyg`.`pyg_profile` (`id`, `uid`, `idnum`, `card`, `create_time`, `update_time`, `delete_time`) VALUES ('2', '2', '435332198108083312', '521656421254777', '1520408547', '1520408547', NULL);
INSERT INTO `pyg`.`pyg_profile` (`id`, `uid`, `idnum`, `card`, `create_time`, `update_time`, `delete_time`) VALUES ('3', '3', '655332198108083357', '681656421254787', '1520408547', '1520408547', NULL);
INSERT INTO `pyg`.`pyg_profile` (`id`, `uid`, `idnum`, `card`, `create_time`, `update_time`, `delete_time`) VALUES ('4', '4', '987067198208083734', '843123421257829', '1520408547', '1520408547', NULL);
INSERT INTO `pyg`.`pyg_profile` (`id`, `uid`, `idnum`, `card`, `create_time`, `update_time`, `delete_time`) VALUES ('5', '5', '657067198408083256', '753623421259523', '1520408547', '1520408547', NULL);
INSERT INTO `pyg`.`pyg_profile` (`id`, `uid`, `idnum`, `card`, `create_time`, `update_time`, `delete_time`) VALUES ('6', '6', '746067198608089463', '534623421259125', '1520408547', '1520408547', NULL);
INSERT INTO `pyg`.`pyg_profile` (`id`, `uid`, `idnum`, `card`, `create_time`, `update_time`, `delete_time`) VALUES ('7', '7', '745367198708089414', '514623426449165', '1520408547', '1520408547', NULL);

创建模型

php think make:model common/Profile

需求：查询管理员信息及其档案信息

首先，定义关联关系

档案表pyg_profile中的uid对应于管理员表中的id

以管理员表为主，一个管理员有一个档案，管理员模型中定义关联关系：

//定义管理员-档案关联关系
public function profile()
{
	return $this->hasOne(Profile::class, 'uid', 'id');
}

方法名，一般和关联的模型名对应，采用首字母小写的驼峰命名法。

三个参数说明

return $this->hasOne(关联model，外键，当前模型主键);

第二个参数，可选，关联的模型的主键（在本模型中的外键）

第三个参数，可选，默认为id（关联模型的主键，可以使用pk属性来设定）

使用模型关联去查询数据

需求：查询管理员数据时，也要查询档案数据

控制器中

$info = \app\common\model\Admin::with('profile')->find(1);
dump($info);
$data = \app\common\model\Admin::with('profile')->select();
dump($data);

定义相对的关联

需求：查询档案信息及管理员信息

档案表pyg_profile中的uid对应于管理员表中的id

以档案表为主，一个管理员有一个档案，管理员模型中定义关联关系：

//定义管理员-档案关联关系
public function admin()
{
	return $this->belongsTo(Admin::class, 'uid', 'id');
}

return $this->hasOne(关联model，关联外键，关联主键);

第二个参数，可选，默认为模型名_id（关联模型的外键）

第三个参数，可选，默认为id（当前模型的主键，可以使用pk来声明）

查询数据

需求：查询档案数据时，也要查询管理员数据

控制器中

$info = \app\common\model\Profile::with('admin')->find(1);
dump($info);
$data = \app\common\model\Profile::with('admin')->select();
dump($data);

模型关联的方式一对多关联

需求：查询商品分类及其下的商品品牌信息

首先，定义关联关系

品牌表中的cate_id对应于分类表中的id

以分类表为主，一个分类下有多个品牌，分类模型中定义关联关系：

//定义分类-品牌关联关系
public function brands()
{
	return $this->hasMany(Brand::class, 'cate_id', 'id');
}

注：方法名，一般和关联的模型名对应，采用首字母小写的驼峰命名法，取复数形式。

return $this->hasMany(关联model，关联外键，关联主键);

第二个参数，可选，默认为模型名_id（关联模型的外键）

第三个参数，可选，默认为id

其次，查询数据

需求：查询分类数据时，也要查询其下的品牌数据

控制器中

$info = \app\common\model\Category::with('brands')->find(72);
dump($info);
$data = \app\common\model\Category::with('brands')->select();
dump($data);

return $this->hasOne(关联model，关联外键，关联主键);

第二个参数，可选，默认为模型名_id（关联模型的外键）

第三个参数，可选，默认为id（当前模型的主键，可以使用pk来声明）

查询数据

需求：查询档案数据时，也要查询管理员数据

控制器中

$info = \app\common\model\Profile::with('admin')->find(1);
dump($info);
$data = \app\common\model\Profile::with('admin')->select();
dump($data);

模型关联的方式一对多关联

需求：查询商品分类及其下的商品品牌信息

首先，定义关联关系

品牌表中的cate_id对应于分类表中的id

以分类表为主，一个分类下有多个品牌，分类模型中定义关联关系：

//定义分类-品牌关联关系
public function brands()
{
	return $this->hasMany(Brand::class, 'cate_id', 'id');
}

注：方法名，一般和关联的模型名对应，采用首字母小写的驼峰命名法，取复数形式。

return $this->hasMany(关联model，关联外键，关联主键);

第二个参数，可选，默认为模型名_id（关联模型的外键）

第三个参数，可选，默认为id

其次，查询数据

需求：查询分类数据时，也要查询其下的品牌数据

控制器中

$info = \app\common\model\Category::with('brands')->find(72);
dump($info);
$data = \app\common\model\Category::with('brands')->select();
dump($data);

绑定属性到父模型

hasOne() 和belongsTo方法后面，调用bind方法，可将属性绑定到父模型中。

注：hasMany方法后不能调用bind方法。

比如，品牌模型中：将分类名称cate_name绑定到品牌模型数据中

public function category()
{
	return $this->BelongsTo(Category::class, 'cate_id')->bind('cate_name');
}

控制器中

$info = \app\common\model\Brand::with('category')->find(1)->toArray();
dump($info);

结果结构如下： cate_name和品牌信息属于同一级

$info = [
    'id'=>1, 
    'name'=>'华为', 
    'cate_name'=>'手机'
];

对比绑定之前：

$info = [
    'id'=>1, 
    'name'=>'华为', 
    'category'=>['cate_name'=>'手机']
];

模型关联的方式多对多关联

清楚一件事：多对多在设计的三范式上一定会产生中间表，中间表放置的就是两个表的主键（外键）

例如，我们的用户和角色就是一种多对多的关系，我们在User模型定义如下：

public function roles()
{
    return $this->belongsToMany(Role::class, 'access','Role模型在access表中的外键','User模型在access表中的外键');
}

belongsToMany('关联模型','中间表','外键','关联键');

关联模型（必须）：关联模型类名
中间表：默认规则是当前模型名+_+关联模型名（可以指定模型名）
外键：中间表的当前模型外键，默认的外键名规则是关联模型名+_id
关联键：中间表的当前模型关联键名，默认规则是当前模型名+_id

使用关联模型

$info = \app\common\model\User::with('roles')

多对多关联不存在拥有和属于的关系，也就是不区分相对关联这个关系，上述案例在Rols模型中写，书写方式也是一样的

深度剖析模型关联的底层执行流程

比如，上面的一个案例：品牌表和商品分类表的关系

一个品牌属于一个分类

一个分类下拥有多个品牌

在使用mysql连表查询时：

SELECT t1.*,t2.cate_name FROM `pyg_brand` t1 left join pyg_category t2 on t1.cate_id = t2.id where t1.id = 1;

一条SQL在千万级、百万级数据面前，就变得非常的慢

使用了模型关联后，查询将被拆分成若干条SQL执行

第一条SQL：

select 分类id from 品牌表 where 品牌id（品牌表的主键） = 1

查询出品牌主键id为1的对应的分类id

第二条SQL：

select 分类名称 from 分类表 where 分类id（分类表的主键）= 上一个SQL查询出的分类id

再次应用分类表中的主键检索分类名称

可以观察得到，上面的两个SQL都在根据主键ID寻找其他非主键列数据，在建表时，主键列自带唯一和主键索引，在查询效率方面是非常高的，而关联查询的精髓就在此处

在框架中开发项目涉及到的表链接要统统使用模型关联，虽然底层会拆分成若干个SQL执行，但也比直接连表查询的效率要快很多

呀哈

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

连表查询与模型关联

连表查询

需求：查询所有商品品牌信息及其所属分类名称

对应框架中的代码：

转化为数组形式的结果

为什么要是用模型关联

模型关联的 方式一对一关联

三个参数说明

模型关联的方式一对多关联

模型关联的方式一对多关联

绑定属性到父模型

模型关联的方式多对多关联

深度剖析模型关联的底层执行流程

模型关联的方式一对一关联