最近前端同事联调时出现了一个错误，让我帮忙查看后，发现是TP框架关联查询时，出现了一个某个字段声明模糊的错误：SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'order_no' in field list is ambiguous
然后立即去检查了代码，发现该接口的部分代码如下：
// 搜索条件
$map[] = ['service_no', '=', $data['service_no']];
if (!is_client_admin()) {
$map[] = ['user_id', '=', get_client_id()];
}
// 关联查询
$with['getUser'] = ['user_id', 'username', 'nickname', 'level_icon', 'head_pic'];
$with['getAdmin'] = ['admin_id', 'username', 'nickname', 'head_pic'];
$with['getOrderGoods'] = [
'order_goods_id', 'goods_name', 'goods_id', 'goods_image',
'key_value', 'qty', 'is_service', 'status','shop_price'
];
// 过滤字段
$field = !is_client_admin() ? 'admin_id,remark,admin_event' : '';
// 实际查询
$result = $this
->with('get_service_log')
->withJoin($with)
->withoutField($field)
->where($map)
->find();
首先看到的是该行代码：
if (!is_client_admin()) {
$map[] = ['user_id', '=', get_client_id()];
}
由于使用了关联查询，在字段查询时，已经自动的加上了别名，在未设置表别名的情况下，默认以表名为前缀，所以第一个问题出现了，此处代码应该是这样的：
# 由于使用了关联查询，在其他表内也存在了user_id的字段，此刻如果不加上相对应表的别名去做区分，也会报in field list is ambiguous的错误
if (!is_client_admin()) {
$map[] = ['zhiyu_blog.user_id', '=', get_client_id()];## 加上相对应的表的别名
}
除了这个，其余的乍一看，好像并没有什么问题，那么我们就先打印一下这个查询的SQL语句看下，由于生成的SQL太长，目前只取有问题的部分，如下(已美化)：
SELECT
`order_service`.`order_service_id`,
`order_service`.`service_no`,
`order_service`.`order_no`,
`order_service`.`order_goods_id`,
`order_service`.`user_id`,
`order_service`.`admin_id`,
`order_service`.`qty`,
`order_service`.`type`,
`order_service`.`reason`,
`order_service`.`description`,
`order_service`.`goods_status`,
`order_service`.`image`,
`order_service`.`status`,
`order_service`.`is_return`,
`order_service`.`result`,
`order_service`.`remark`,
`order_service`.`refund_fee`,
`order_service`.`refund_detail`,
`order_service`.`refund_no`,
`order_service`.`address`,
`order_service`.`consignee`,
`order_service`.`zipcode`,
`order_service`.`mobile`,
`order_service`.`logistic_code`,
`order_service`.`delivery_fee`,
`order_service`.`admin_event`,
`order_service`.`user_event`,
`order_service`.`create_time`,
`order_service`.`update_time`,
`order_service`.`refund_apply_oms_status`,
`order_service`.`refund_update_oms_status`,
`order_service`.`oms_return_bn`,
`getUser`.`user_id` AS `getUser__user_id`,
`getUser`.`username` AS `getUser__username`,
`getUser`.`nickname` AS `getUser__nickname`,
`getUser`.`level_icon` AS `getUser__level_icon`,
`getUser`.`head_pic` AS `getUser__head_pic`,
`getAdmin`.`admin_id` AS `getAdmin__admin_id`,
`getAdmin`.`username` AS `getAdmin__username`,
`getAdmin`.`nickname` AS `getAdmin__nickname`,
`getAdmin`.`head_pic` AS `getAdmin__head_pic`,
`getOrderGoods`.`order_goods_id` AS `getOrderGoods__order_goods_id`,
`getOrderGoods`.`goods_name` AS `getOrderGoods__goods_name`,
`getOrderGoods`.`goods_id` AS `getOrderGoods__goods_id`,
`getOrderGoods`.`goods_image` AS `getOrderGoods__goods_image`,
`getOrderGoods`.`key_value` AS `getOrderGoods__key_value`,
`getOrderGoods`.`qty` AS `getOrderGoods__qty`,
`getOrderGoods`.`is_service` AS `getOrderGoods__is_service`,
`getOrderGoods`.`status` AS `getOrderGoods__status`,
`getOrderGoods`.`shop_price` AS `getOrderGoods__shop_price`,
`order_service_id`,
`service_no`,
`order_no`,
`order_goods_id`,
`user_id`,
`qty`,
`type`,
`reason`,
`description`,
`goods_status`,
`image`,
`status`,
`is_return`,
`result`,
`refund_fee`,
`refund_detail`,
`refund_no`,
`address`,
`consignee`,
`zipcode`,
`mobile`,
`logistic_code`,
`delivery_fee`,
`user_event`,
`create_time`,
`update_time`,
`refund_apply_oms_status`,
`refund_update_oms_status`,
`oms_return_bn`
FROM
`zy_order_service` `order_service`
LEFT JOIN `zy_user` `getUser` ON `order_service`.`user_id` = `getUser`.`user_id`
LEFT JOIN `zy_admin` `getAdmin` ON `order_service`.`admin_id` = `getAdmin`.`admin_id`
INNER JOIN `zy_order_goods` `getOrderGoods` ON `order_service`.`order_goods_id` = `getOrderGoods`.`order_goods_id`
通过生成的SQL我们可以看出，查询的字段内，有一部分字段重复了，但是重复的部分并没有下面用到表的相对应别名来对应查询的哪张表的数据，接口的报错就是因为查询了不明确的字段数据导致，那么我们回到代码层面来看相关的查询部分：
$result = $this->with('get_service_log')->withJoin($with)->withoutField($field)
->where($map)->find();
上述的问题是因为某些字段查询重复，且没有进行相对应的处理导致，那我们就快速的去看下$this后几个方法，都有谁涉及到了field的处理，查询到关键结点即可解决问题。
首先，我们查看with()做的相关处理：
# 通过代码发现，with的操作只是做了参数的绑定，没有涉及到field的操作
public function with($with)
{
if (empty($this->model) || empty($with)) {
return $this;
}
$this->options['with'] = (array) $with;
return $this;
}
然后，我们查看withJoin()做的相关处理：
public function withJoin($with, string $joinType = '')
{
# .....省略部分代码.....
$first = true; # 当前问题的重点记住这个true
foreach ($with as $key => $relation) {
# .....省略部分代码.....
$result = $this->model->eagerly($this, $relation, $field, $joinType, $closure, $first); # 此处我们看到有对field的操作，上面具体的操作暂时不去查看，继续跟对应代码
if (!$result) {
unset($with[$key]);
} else {
$first = false;
}
}
# .....省略部分代码.....
return $this;
}
### 通过查看代码，我们发现，上述操作最终在该方法完成一系列拼接
public function eagerly(Query $query, string $relation, $field = true, string $joinType = '', Closure $closure = null, bool $first = false): void
{
# .....省略部分代码.....
if ($first) { # 首次查询时
$table = $query->getTable(); # 首先获取当前表的表名($query对应$this)
$query->table([$table => $name]);
if ($query->getOptions('field')) { # 此处去判断当前是否已经设置了查询参数field
$masterField = $query->getOptions('field'); # 获取当前要查询的字段
$query->removeOption('field'); # 清除掉 要做连边查询
} else {
$masterField = true;
}
$query->tableField($masterField, $table, $name); # 对当前表查询字段做处理(对查询字段之类的加别名等操作)
}
# .....省略部分代码.....
}
通过上述的代码查看，我们可以看到，在withJoin()第一次进行处理时，eagerly()内首先去对当前表做了相对应处理，我们打印一下此刻查询参数的数据：
# $result = $this->with('get_service_log')->withJoin($with);
# var_dump($result->getOptions());
# 上述输出的部分结果(field部分)如下
["field"]=>
array(47) {
[0]=>
string(30) "order_service.order_service_id"
[1]=>
string(24) "order_service.service_no"
[2]=>
string(22) "order_service.order_no"
[3]=>
string(28) "order_service.order_goods_id"
[4]=>
string(21) "order_service.user_id"
[5]=>
string(17) "order_service.qty"
[6]=>
string(18) "order_service.type"
[7]=>
string(20) "order_service.reason"
[8]=>
string(25) "order_service.description"
[9]=>
string(26) "order_service.goods_status"
[10]=>
string(19) "order_service.image"
[11]=>
string(20) "order_service.status"
[12]=>
string(23) "order_service.is_return"
[13]=>
string(20) "order_service.result"
[14]=>
string(24) "order_service.refund_fee"
[15]=>
string(27) "order_service.refund_detail"
[16]=>
string(23) "order_service.refund_no"
[17]=>
string(21) "order_service.address"
[18]=>
string(23) "order_service.consignee"
[19]=>
string(21) "order_service.zipcode"
[20]=>
string(20) "order_service.mobile"
[21]=>
string(27) "order_service.logistic_code"
[22]=>
string(26) "order_service.delivery_fee"
[23]=>
string(24) "order_service.user_event"
[24]=>
string(25) "order_service.create_time"
[25]=>
string(25) "order_service.update_time"
[26]=>
string(37) "order_service.refund_apply_oms_status"
[27]=>
string(38) "order_service.refund_update_oms_status"
[28]=>
string(27) "order_service.oms_return_bn"
["getUser.user_id"]=>
string(16) "getUser__user_id"
["getUser.username"]=>
string(17) "getUser__username"
["getUser.nickname"]=>
string(17) "getUser__nickname"
["getUser.level_icon"]=>
string(19) "getUser__level_icon"
["getUser.head_pic"]=>
string(17) "getUser__head_pic"
["getAdmin.admin_id"]=>
string(18) "getAdmin__admin_id"
["getAdmin.username"]=>
string(18) "getAdmin__username"
["getAdmin.nickname"]=>
string(18) "getAdmin__nickname"
["getAdmin.head_pic"]=>
string(18) "getAdmin__head_pic"
["getOrderGoods.order_goods_id"]=>
string(29) "getOrderGoods__order_goods_id"
["getOrderGoods.goods_name"]=>
string(25) "getOrderGoods__goods_name"
["getOrderGoods.goods_id"]=>
string(23) "getOrderGoods__goods_id"
["getOrderGoods.goods_image"]=>
string(26) "getOrderGoods__goods_image"
["getOrderGoods.key_value"]=>
string(24) "getOrderGoods__key_value"
["getOrderGoods.qty"]=>
string(18) "getOrderGoods__qty"
["getOrderGoods.is_service"]=>
string(25) "getOrderGoods__is_service"
["getOrderGoods.status"]=>
string(21) "getOrderGoods__status"
["getOrderGoods.shop_price"]=>
string(25) "getOrderGoods__shop_price"
}
到了此刻，其实不难发现，如果此刻去查询数据的话，是不会出现问题的，那么就可以确定问题就出在withJoin()之后了，其后还剩下三个用到的方法，可以直接排除的是where()和find()，那么出现问题的就在withoutField()了，我们查看一下该方法所做的处理：
public function withoutField($field)
{
if (empty($field)) {
return $this;
}
if (is_string($field)) {
$field = array_map('trim', explode(',', $field));
}
// 字段排除
$fields = $this->getTableFields(); ## 获取当前表的所有字段
$field = $fields ? array_diff($fields, $field) : $field; # 将要过滤的字段从所有字段内排除
if (isset($this->options['field'])) { ## 重点！
$field = array_merge((array) $this->options['field'], $field); # 将之前查询参数已有的字段与字段过滤后的字段进行合并---此时要注意，上述获取所有字段的时候，获取的字段是没有加表别名的！！
}
$this->options['field'] = array_unique($field); # 字段去重
return $this;
}
因为在执行该方法之前，我们在withJoin()内对查询参数field做了设置，此时isset($this->options['field'])判断是成立的，就造成了没有加过表别名的字段和已经加过表别名的字段合并到了一起，且
在去重时，去重无效
到了此刻，我们已经找到了出现问题的地方，只需要将代码稍微修改一下即可：
$result = $this->with('get_service_log')->withoutField($field)->withJoin($with)
->where($map)->find();
## 或者
$result = $this->withoutField($field)->with('get_service_log')->withJoin($with)
->where($map)->find();
发表评论