连表查询

简介

使用联表查询可以用简单易懂的语法写出功能强大的联表操作,支持一对一,多对一,一对多,多对多等多种数据表关联,并且自动优化生成的SQL,既提高开发效率又提升代码运行效率。

联表查询类似于ActiveRecordORM中的hasOne hasMany belongsTo belongsToMany等设定,相对ORM优点是不用生成大量对象,也不用写model模型代码定义每个表和表之间关系。

写一个简单的例子,如一个在线社区论坛有多个主题贴子,每一个主题也有多个回复评论,如果我一次想要查询自己所有的主题贴子和所有的主题贴子的回复评论回复怎么办?

$posts = $db->post->where('name', 'qiujin')->find();
foreach ($posts as $i => $post) {
$posts[$i]['comment'] = db->comment->where('post_id', $post['id'])->find();
}
return $posts;
return $db->post->where('name', 'qiujin')->with('comment')->find();

对比就可以知道常规写法不仅啰嗦,而且低效,因为常规写法执行了1+NSQL

with语句简洁明了,查询 post 数据后,在把 post 数据中的 id 抽出组成 where in 查询 comment 表数据,最后把 comment 数据组合分配给 post 返回,其中只执行了 1+1SQL,比常规写法效率高很多。

Join方法

生成原生JOIN语句SQL来联表查询多表数据.

通常用于一对一和多对一联表场合

一对一:查询用户信息与其帐号积分信息(一个用户只有一个积分帐号表)

$db->user->join('account')->select('score')->get($user_id);
SELECT `user`.*,`account`.`score` AS `account_score` FROM `user` LEFT JOIN `account` ON `user`.`id` = `account`.`user_id` WHERE `user`.`id` = '1'

多对一:查询最近10个主题以及发布者信息(一个用户可以发布多个主题)

$db->post->order('id', true)->limit(10)->join('user')->on('user_id', 'id')->select('id', 'name')->find();
SELECT `post`.*,`user`.`id` AS `user_id`,`user`.`name` AS `user_name` FROM `post` LEFT JOIN `user` ON `post`.`user_id` = `user`.`id` ORDER BY `post`.`id` DESC LIMIT 10

join方法有3个参数

第1个参数是要join的表名

第2个参数是join类型(支持INNER LEFT RIGHT,默认为LEFT

第3个参数是表的别名(默认为空)

从属on方法,用于指定主表与从表的关联字段,在符合默认设定的情况下无需使用。

on方法有2个参数,第1个参数是主表的关联字段,第2个参数是从表的关联字段

另外join支持select(false)写法,用于设置对应作用域的表不返回任何字段查询结果(不设置则返回所有字段查询结果)

With方法

使用逻辑联表查询多表数据。

通常用于一对多和多对多表关系场合

在默认优化条件下只需要1+1次SQL查询,先查主表数据,然后根据主表数据where in查询从表数据,最后逻辑组合2表数据。

例:查询一个用户及其最近10个主题

$db->user->with('post')->order('id', true)->limit(10)->get($user_id)
SELECT * FROM `user` WHERE `id` = '1' LIMIT 1
SELECT * FROM `post` WHERE `user_id` = '1' ORDER BY `id` DESC LIMIT 10

例:查询主题以及回复评论第1页

$db->post->with('comment')->page(1)->get($post_id)
SELECT * FROM `post` WHERE `id` = '1' LIMIT 1
SELECT * FROM `comment` WHERE `post_id` = '1' LIMIT 0,30

with方法有2个参数

第1个参数是表名

第2个参数是表的别名(默认为空)

从属on方法,用于指定主表与从表的关联字段,在符合默认设定的情况下无需使用。

on方法有2个参数,第1个参数是主表的关联字段,第2个参数是从表的关联字段

relate方法

通常用于多对多表关系场合,并且有一个关系表存储2个表的对应关系。

在默认优化条件下只需要1+1+1次SQL查询,先查主表数据,在查关系表数据,然后根据关系表数据查询从表数据,最后逻辑组合3表数据。

例:查询一个用户及其最近收藏书签的主题,其中bookmark书签表是关系表保存userpost多对多的映射关系。

$db->user->relate('post')->on('bookmark')->get($user_id);
SELECT * FROM `user` WHERE `id` = '1' LIMIT 1
SELECT `user_id`, `post_id` FROM `bookmark` WHERE `user_id` IN ('1')
SELECT * FROM `post` WHERE `id` IN ('2','3','5')

relate方法有2个参数

第1个参数是表名

第2个参数是表的别名(默认为空)

从属on方法,用于指定主表与从表的关系表与关联字段,在符合默认设定的情况下无需使用。

on方法有3个参数,

第1个参数是主表和从表的关系表的表名,

第2个参数是主表与关系表的关联字段数组

第3个参数是从表与关系表的关联字段数组

子查询

生成原生SQL子查询语句联表查询主表数据。

子查询通常只做为主表的过滤条件,用户不需要其本身数据。

例:查询最新一个主题的作者的信息。

$db->user->sub('post')->order('id', ture)->get()
SELECT * FROM `user` WHERE `id` IN (SELECT `user_id` FROM `post` ORDER BY `id` DESC) LIMIT 1

子查询sub方法有3个参数,后2个参数为可选参数。

第1个参数是子查询表名

第2个参数是子查询做为查询条件并入主表的where表达式(默认为IN),参数集合['=', '>', '<', '>=', '<=', '<>', 'ANY', 'IN', 'SOME', 'ALL', 'EXISTS']

第2个参数是子查询做为查询条件并入主表的where逻辑组合默认为AND),参数集合['AND', 'OR', 'XOR', 'AND NOT', 'OR NOT', 'NOT']

从属on方法,用于指定主表与从表的关联字段,在符合默认设定的情况下无需使用。

on方法有2个参数,第1个参数是主表的关联字段,第2个参数是从表的关联字段

另外子查询除了支持get find等查询操作,也支持update更新操作和delete删除操作。

union方法

使用原生SQL union语法联表查询主表数据。

union用于表结构相同的多个表。

$db->table1->union('table2')->union('table3')->find()

union 方法较简单,支持一个参数指定union的表名,也没有从属on方法,并且只支持find查询方法。

作用域

作用域的存在避免了在联表查询的链式方法中显式的申明表名,使联表查询语句简洁易懂。

在作用域内where select order limit等方法不需要显式的指定表名,因为作用域确定了这些方法是作用于那张表,并在生成SQL时自动处理。

示例:查找积分大于0的用户,及其在2017-10-01后发表的主题。

$db->user->select('id', 'name')// 查找用户id name
->sub('account')->where('score', '>', 0)// 查找用户积分score大于0的记录
->with('post')->select('id', 'title')->where('time', '>', '2017-10-01') //查找主表用户2017-10-01后发布主题的id和标题
->find(); //执行查询
// 生成并执行的SQL
SELECT `id`,`name` FROM `user` WHERE `id` IN (SELECT `user_id` FROM `account` WHERE `score` > '0')
SELECT `id`,`title`,`user_id` FROM `post` WHERE `user_id` IN ('1','2','3','5') AND `time` > '2017-10-01'

下面解析下示例中的作用域

  1. 主表user作用域,$db->user后就进入主表user的作用域,其后接方法select('id', 'name')是作用于主表user也就是查询主表的idname,直到遇到连表方法才跳出主表作用域进入从表作用域。

  2. 子查询从表account作用域,sub('account')进入子查询从表account作用域,其后接方法where('score', '>', 0)作用于从表account,直到遇到其它连表方法才跳出其作用域进入其它从表作用域。

  3. with联表查询从表post作用域,with('post')进入with连表查询从表post作用域,select('id', 'title')where('time', '>', $time)作用于从表post

  4. 最后的查询方法find作用域为主表,连表查询最后查询方法get find等的作用域又跳回主表,其参数只作用于主表。。

关联字段

当使用多个数据表进行联表查询时,表与表之间的关系时如何确定的,是通过什么字段将表关联起来的的呢?

on方法

为了处理这个问题,每个联表方法都有自己特有从属on方法,此方法就是用来指定表之间的关联字段。

如例:查询一个主题贴及其发表者

$db->user->with('post')->on('id', 'user_id')->get($user_id)

例中的post表和user表的关联字段就使用了on方法指定user.id关联post.user_id

默认关联

默认关联规则为:主表的id字段默认关联从表中的主表名+id的字段。

使用默认关联的规则,大部分情况下我们都无需使用on方法来指定关联字段,只要表与表之间符合默认关联的规则就可以省略on方法。

如上一个例子,我们也可以这样写。

$db->user->with('post')->get($user_id)

例中->on('id', 'user_id')被省略了,因为使用了默认关联规则:user.id默认关联post.user_id

另外为了能方便使用默认关联,数据库字段名的设计最好符合以下2点。

1 表主键名为id

2 表外键名为表名+id,如user_id(建议表名为单数,不然用users_id挺别扭)

多重联表

在大部分情况,我们只联2张表查询就基本满足大部分应用需求,但是有时候也可能有3张表及以上的联表查询需求。

联表查询3表及以上就叫做多重联表

多重联表示例:查询主题1及其发布者与评论信息

$db->post->join('user')->on('user_id', 'id')->select('name')->with('comment')->get(1)
// 生成并执行的SQL
SELECT `post`.*,`user`.`name` AS `user_name` FROM `post` LEFT JOIN `user` ON `post`.`user_id` = `user`.`id` WHERE `post`.`id` = '1'
SELECT * FROM `comment` WHERE `post_id` = '1'

例中关联到了post user comment 3个表,其中post作为主表,userjoin从表,commentwith从表。

post表与user表是多对一关系,一个post只有一个user发表者,一个user能发表多个post主题。

post表与comment表是一对多关系,一个post有多条comment评论,一条comment只属于一个post主题。

先主表post join user表查询数据,表之间使用on方法关联字段,得到查询数据后使用with逻辑联表方法查询omment数据,表之间关联使用默认设定,然后组合数据返回。


join sub union方法支持多重联表查询(但不支持混用)


join sub union方法后也支持with relate方法