ThinkPHP+MySQL查询数据的时候计算两个经纬度之间的距离并根据距离进行筛选

原需求实现说明

新增了一个按距离进行筛选的需求。需要把查询代码做如下修改

    /**
     * 求职意向
     * @return void
     * @throws \think\exception\DbException
     */
    public function get_lists(){
        $request = $this->request->get();
        if(empty($request['lng']) || empty($request['lat'])){
            $this->error('位置信息错误');
        }
        $where = [];
        //筛选城市
        if(!empty($request['city'])){
            $where['p.city'] = array('eq',$request['city']);
        }
        //筛选姓名或者岗位名
        if(!empty($request['keywords'])){
            $where['r.name|c.name'] = array('like','%'.$request['keywords'].'%');
        }
        //筛选工种
        if(!empty($request['gongzhong'])){
            $where['p.gongzhong'] = array('eq',$request['gongzhong']);
        }
        //筛选性别
        if(isset($request['sex']) && in_array($request['sex'],[0,1])){
            $where['r.sex'] = array('eq',$request['sex']);
        }

        //筛选距离
        $sql = '';
        if(!empty($request['distance'])){
            $sql = "ROUND(
                    6378.138 * 2 * ASIN(
                        SQRT(
                            POW(SIN((".$request['lat']." * PI() / 180 - p.lat * PI() / 180) / 2), 2) +
                            COS(".$request['lat']." * PI() / 180) * COS(p.lat * PI() / 180) * POW(SIN((".$request['lng']." * PI() / 180 - p.lng * PI() / 180) / 2), 2)
                        )
                    ) * 1000
                ) < ".$request['distance'];
        }
        //最高学历筛选
        if(!empty($request['zuigaoxueli'])){
            $where['r.zuigaoxueli'] = array('eq',$request['zuigaoxueli']);
        }
        $lists = PurposeModel::alias('p')
            ->join('user u','p.user_id = u.id')
            ->join('resume r','p.user_id = r.user_id')
            ->join('category c','p.category_id = c.id')
            //根据生日计算年领(YEAR(CURDATE()) - YEAR(birthday)) AS age 根据经纬度计算距离
            ->field('p.user_id,r.name,u.avatar,(YEAR(CURDATE()) - YEAR(birthday)) AS age,r.sex,r.zuigaoxueli,c.name as category_name,p.gongzhong,p.work_begin,p.work_end,p.rest_begint,p.rest_end,p.city,p.address,'.getDistanceBuilder($request['lat'], $request['lng']))
            ->where($where)
            ->where($sql)
            ->order('distance asc')
            ->paginate();
        $this->success('获取成功',$lists);
    }

得到的SQL如下

SELECT 
    p.user_id,
    r.name,
    u.avatar,
    (YEAR(CURDATE()) - YEAR(birthday)) AS age,
    r.sex,
    c.name AS category_name,
    p.gongzhong,
    p.work_begin,
    p.work_end,
    p.rest_begint,
    p.rest_end,
    p.city,
    p.address,
    ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(SIN((34.781089 * PI() / 180 - p.lat * PI() / 180) / 2), 2) +
                COS(34.781089 * PI() / 180) * COS(p.lat * PI() / 180) * POW(SIN((113.61261 * PI() / 180 - p.lng * PI() / 180) / 2), 2)
            )
        ) * 1000
    ) AS distance
FROM 
    `fa_purpose` `p`
INNER JOIN 
    `fa_user` `u` ON `p`.`user_id` = `u`.`id`
INNER JOIN 
    `fa_resume` `r` ON `p`.`user_id` = `r`.`user_id`
INNER JOIN 
    `fa_category` `c` ON `p`.`category_id` = `c`.`id`
WHERE 
    `p`.`city` = '郑州'  
    AND (`r`.`name` LIKE '%无人机%' OR `c`.`name` LIKE '%无人机%')
    AND `p`.`gongzhong` = '1'
    AND `r`.`sex` = '0'
    AND ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(SIN((34.781089 * PI() / 180 - p.lat * PI() / 180) / 2), 2) +
                COS(34.781089 * PI() / 180) * COS(p.lat * PI() / 180) * POW(SIN((113.61261 * PI() / 180 - p.lng * PI() / 180) / 2), 2)
            )
        ) * 1000
    ) < 500
ORDER BY 
    distance ASC