欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL多表联查之ThinkPHP中的实现

程序员文章站 2022-06-26 09:12:10
创建两个表如图: sp_user表: sp_dept表: 目的:通过sp_user的dept_id查询所属部门即sp_dept中的name。 原生sq方法一:select t1.*,t2.name as deptname from sp_user as t1,sp_dept as t2 where ......

创建两个表如图:

sp_user表:MySQL多表联查之ThinkPHP中的实现

sp_dept表:MySQL多表联查之ThinkPHP中的实现

目的:通过sp_user的dept_id查询所属部门即sp_dept中的name。

原生sq方法一:select t1.*,t2.name as deptname from sp_user as t1,sp_dept as t2 where t1.dept_id = t2.id;

原生sq方法二:select t1.*,t2.name as deptname from sp_user as t1 left join sp_dept as t2 on t1.dept_id = t2.id;

结果相同:MySQL多表联查之ThinkPHP中的实现

对应thinkphp中,则也有两种方法table和join如下:

 1 //多表联查(table)
 2     public function test18() {
 3         //实例化模型
 4         $model = m();
 5         //查询
 6         $result = $model->field('t1.*, t2.name as deptname')->table('sp_user as t1, sp_dept as t2')->where('t1.dept_id = t2.id')->select();
 7         dump($result);
 8     }
 9     //多表联查(join)
10     public function test19() {
11         //实例化模型
12         $model = m('user');
13         //查询
14         $result = $model->field('t1.*, t2.name as deptname')->alias('t1')->join('left join sp_dept as t2 on t1.dept_id = t2.id')->select();
15         dump($result);
16     }

测试结果相同:

MySQL多表联查之ThinkPHP中的实现MySQL多表联查之ThinkPHP中的实现

 

综上:thinkphp进行多表联查可通过table方法和join方法分别实现,测试环境thinkphp3.2.3,表前缀sp_

MySQL多表联查之ThinkPHP中的实现