ThinkPHP5数据库高级查询记录

问题

在最近的一次内部系统对外提供API接口的查询中有这么一个需求

上图中的投资金额、投资区域、分别是brand表（下面简称b表）中的join_fee和join_area字段，意向品类则是一张多对多的关联表。

而当输入关键词的时候，需要分别去检索b表中的brand_name检索，而且还需要去brand_ask表（下面简称ba表）中去检索其ask字段。

当时写的时候就很想当然的使用了下面的方式

.
.
.
$query->join('fa_cm_brand_ask ba', 'ba.bid = b.id', 'LEFT');
if ($keyword) {
    $query->where('b.brand_name', 'LIKE', "%{$keyword}%")
          ->whereOR('ba.ask', 'LIKE', "%{$keyword}%");

}
.
.
.

然后查看sql打印日志发现构建的sql如下

SELECT
    `b`.`id`,
    `b`.`brand_name`,
    `b`.`join_fee`,
    `b`.`join_area`,
    `b`.`create_time`,
    bc.id category_id,
    `bc`.`category_name`,
    min(`ba`.`type_id`) AS type_id
FROM
    `fa_cm_brand` `b`
LEFT JOIN `fa_cm_brand_category_relation` `bcr` ON `b`.`id` = `bcr`.`bid`
LEFT JOIN `fa_brand_category` `bc` ON `bc`.`id` = `bcr`.`category_id`
LEFT JOIN `fa_cm_brand_ask` `ba` ON `ba`.`bid` = `b`.`id`
WHERE
    `bc`.`id` = 20
AND `b`.`join_fee` = '3'
AND `b`.`brand_name` LIKE '%本%' --- 这里有问题
OR `ba`.`ask` LIKE '%本%' --- 还有这行
GROUP BY
    `b`.`brand_name`
ORDER BY
    `b`.`create_time` DESC
LIMIT 8

由于当时没有完全理解需求，上面的关键词输入框是要求b.brand_name和ba.ask整体和其他条件组成AND关系，也就是正常的sql大概是下面这样(删除重复部分)

WHERE
    `bc`.`id` = 20
AND `b`.`join_fee` = '3'
AND ( `b`.`brand_name` LIKE '%本%'
OR `ba`.`ask` LIKE '%本%' )

少了这么一对括号，加上括号逻辑就完全不一样了。然后剩下的问题就是怎么把这一对小括号给整上去。

方案

由于平时写原生的sql比较多，这种框架级别的sql拼装有点遗忘了。但是之前在使用laravel中有印象的一个单词是raw，所以试着搜了下thinkphp5 orm原生或者thinkphp5 whereraw，然后发现了确实有一个whereRaw方法可以使用。

if ($keyword) {
    $query->whereRaw("`b`.`brand_name` LIKE '%{$keyword}%' OR `ba`.`ask` LIKE '%{$keyword}%'");
}

打印sql日志完整输出如下：

SELECT
    `b`.`id`,
    `b`.`brand_name`,
    `b`.`join_fee`,
    `b`.`join_area`,
    `b`.`create_time`,
    bc.id category_id,
    `bc`.`category_name`,
    min(`ba`.`type_id`) AS type_id
FROM
    `fa_cm_brand` `b`
LEFT JOIN `fa_cm_brand_category_relation` `bcr` ON `b`.`id` = `bcr`.`bid`
LEFT JOIN `fa_brand_category` `bc` ON `bc`.`id` = `bcr`.`category_id`
LEFT JOIN `fa_cm_brand_ask` `ba` ON `ba`.`bid` = `b`.`id`
WHERE
    `bc`.`id` = 20
AND `b`.`join_fee` = '3'
AND ( --- 注意这里
    `b`.`brand_name` LIKE '%本%'
    OR `ba`.`ask` LIKE '%本%'
) --- 注意这里
GROUP BY
    `b`.`brand_name`
ORDER BY
    `b`.`create_time` DESC
LIMIT 8

从上面sql输出可以看到就简单的加上了一对括号，还是很方便的。

后记

这个问题解决以后，又翻看了TP5框架数据库相关文档，然后也发现了另外一种写法也可以实现本次对应需求，也一起记录一下。

if ($keyword) {
    // 快捷高级查询 与上面的相同 (参考:https://www.kancloud.cn/manual/thinkphp5/135184)
    $query->where('b.brand_name|ba.ask', 'LIKE', "%$keyword%");
}

这种方式和上面输出的sql是一模一样的。

总结

以前用laravel的时候还是很喜欢用ORM或者DB类的，但是这2年来公司的项目由于历史原因没怎么使用PHP社区相对主流的框架来开发，主要还是写原生SQL多。倒是本次重新切换到框架的数据查询模式的时候有很多好用的方法都遗忘了。不过好在脑海里还是有印象的，用的时候也可以很快的搜索到。

所以不要忘了，我们是面向Google编程，哈哈哈。

---

CREATE TABLE fa_cm_brand (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '品牌id',
brand_name varchar(100) NOT NULL DEFAULT '' COMMENT '品牌名称',
brand_logo varchar(255) NOT NULL DEFAULT '' COMMENT '品牌logo',
company_name varchar(100) NOT NULL DEFAULT '' COMMENT '公司名称',
company_city varchar(255) NOT NULL DEFAULT '' COMMENT '总部城市',
company_phone varchar(20) NOT NULL DEFAULT '' COMMENT '公司电话',
company_address varchar(255) NOT NULL DEFAULT '' COMMENT '公司地址',
company_create_time varchar(100) NOT NULL DEFAULT '' COMMENT '公司成立时间',
company_site varchar(100) NOT NULL DEFAULT '' COMMENT '公司网址',
join_fee tinyint(4) NOT NULL DEFAULT '0' COMMENT '投资金额 1:5万以下 2:5-10万 3:10万以上',
join_area varchar(255) DEFAULT '' COMMENT '加盟区域',
create_time int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
update_time int(10) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
admin_id int(10) NOT NULL DEFAULT '0' COMMENT '提交人id',
status tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '状态 1正常',
PRIMARY KEY (id),
KEY idx_bname (brand_name),
KEY idx_jfee (join_fee),
KEY idx_jarea (join_area)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COMMENT='餐盟品牌表';

CREATE TABLE fa_cm_brand_ask (
id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
bid int(10) NOT NULL DEFAULT '0' COMMENT '品牌id',
type_id int(10) NOT NULL DEFAULT '0' COMMENT '种类 关联fa_cm_type.id',
ask varchar(255) NOT NULL DEFAULT '' COMMENT '问题',
answer text COMMENT '回答',
create_time int(10) unsigned DEFAULT '0' COMMENT '创建时间',
update_time int(10) unsigned DEFAULT '0' COMMENT '更新时间',
PRIMARY KEY (id) USING BTREE,
KEY idx_bid (bid),
KEY idx_ask (ask)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COMMENT='品牌详情表';