TP5 Sql报错 having新参数之后 无法使用count统计解决方案

未定义数组索引: distance

SQLSTATE[HY000]: General error: 2031 No data supplied for parameters in prepared statement

在一个需要根据用户当前位置计算距离的场景下 我们需要使用having这样的函数去做一个计算 但是刚好又是一个列表需要分页 需要总页数 此时会遇到如上两个报错 导致无法统计总共有多少条 以下是解决这个问题的一个方法  

public function get_list()
    {
        // 获取参数
        $params = [
            'page' => $this->request->post('page') ?? 1,
            'limit' => $this->request->post('limit') ?? 10,
            'name' => $this->request->post('name') ?? '',
            'tag' => $this->request->post('tag') ?? '',
            'province' => $this->request->post('province') ?? '',
            'city' => $this->request->post('city') ?? '',
            'district' => $this->request->post('district') ?? '',
            'create_type' => $this->request->post('create_type') ?? 0,
            'distance_type' => $this->request->post('distance_type') ?? 0,
            'shop_type' => $this->request->post('shop_type') ?? 0,
            'lng' => $this->request->post('lng') ?? '',
            'lat' => $this->request->post('lat') ?? ''
        ];

        // 校验参数
        $result = $this->validate([
            'lng' => $params['lng'],
            'lat' => $params['lat'],
            'page' => $params['page'],
            'limit' => $params['limit'],
        ], [
            'lng' => 'require',
            'lat' => 'require',
            'page' => 'require',
            'limit' => 'require',
        ], [
            'lng.require' => '请传入经度',
            'lat.require' => '请传入纬度',
            'page.require' => 'page参数缺失',
            'limit.require' => 'limit参数缺失',
        ]);

        if (true !== $result) {
            $this->error($result);
        }

        // 公共查询条件构建
        $buildQuery = function($query) use ($params) {
            // 基本条件
            $query->where('status', 1)
                ->where('private_user_id', '');

            // 搜索条件
            if ($params['name'] != '') {
                $query->where('name', 'like', '%' . $params['name'] . '%');
            }
            if ($params['tag'] != '') {
                $query->where('tag', $params['tag']);
            }
            if ($params['province'] != '') {
                $query->where('province', $params['province']);
            }
            if ($params['city'] != '') {
                $query->where('city', $params['city']);
            }
            if ($params['district'] != '') {
                $query->where('district', $params['district']);
            }

            // 店铺类型
            if ($params['shop_type'] > 0) {
                $query->where('shop_type', $params['shop_type']);
            }

            // 创建时间类型
            switch ($params['create_type']) {
                case 1: // 今日新增
                    $query->where('createtime', '>=', strtotime(date('Y-m-d')));
                    break;
                case 2: // 近两天
                    $query->where('createtime', '>=', strtotime(date('Y-m-d') - 86400));
                    break;
                case 3: // 近三天
                    $query->where('createtime', '>=', strtotime(date('Y-m-d') - 86400 * 2));
                    break;
                case 4: // 近30天
                    $query->where('createtime', '>=', strtotime(date('Y-m-d') - 86400 * 30));
                    break;
                case 5: // 最新创建
                    $query->order('createtime desc');
                    break;
            }

            // 距离计算字段
            $query->field([
                '*',
                "(6371 * ACOS(
                COS(RADIANS({$params['lat']})) * COS(RADIANS(lat)) *
                COS(RADIANS(lng) - RADIANS({$params['lng']})) +
                SIN(RADIANS({$params['lat']})) * SIN(RADIANS(lat))
            )) AS distance"
            ]);

            // 距离类型
            switch ($params['distance_type']) {
                case 1: // 最近距离
                    $query->having('distance IS NOT NULL')
                        ->order('distance', 'ASC');
                    break;
                case 2: // 最新录入
                    $query->having('distance IS NOT NULL AND distance <= 30')
                        ->order('createtime desc');
                    break;
                default:
                    $query->having('distance IS NOT NULL');
                    break;
            }

            return $query;
        };

        // 获取分页数据
        $data = $buildQuery(Db::name('shop_lists'))
            ->page($params['page'], $params['limit'])
            ->select();

        // 获取总数
        $total = $buildQuery(Db::name('shop_lists'))
            ->select();

        // 数据处理
        foreach ($data as &$v) {
            // 处理图片
            $v['storefront_images'] = array_map(function($item) {
                return cdnurl($item, true);
            }, explode(',', $v['storefront_images']));

            $v['environment_images'] = array_map(function($item) {
                return cdnurl($item, true);
            }, explode(',', $v['environment_images']));

            // 格式化时间
            $v['createtime'] = date('Y-m-d H:i:s', $v['createtime']);

            // 获取标签信息
            $v['shop_tag_ids'] = Db::name('shop_tag')
                ->whereIn('id', $v['shop_tag_ids'])
                ->field('id,name')
                ->select();
            $v['tag'] = Db::name('user_tag')
                ->where('id', $v['user_tag_id'])
                ->value('name');

            // 处理手机号标签
            $v['mobile_tags'] = array_map(function($item) {
                return $this->maskString($item, 5, 3, 2);
            }, explode(',', $v['mobile_tags']));

            // 信息隐藏
            $v['name'] = $this->maskString($v['name']);
            $v['address'] = $this->maskString($v['address'], 5, 3, 0);

            // 保留两位小数
            $v['distance'] = number_format($v['distance'], 2);
        }

        $this->success('请求成功', [
            'rows' => $data,
            'total' => count($total),
        ]);
    }