thinkphp5.0.20表达式查询

直接上代码，欢迎大家阅读

    public function workTopList()
    {
        $user_id = input('param.user_id');

        //排序   0序号(大-小)  1距离排序(从小到大)   2最新发布(addtime从大到小)   3热门兼职(is_urgent)
        $sort = trim(input('param.sort'));

        //用户当时的经纬度信息
        $latitude = input('param.latitude');
        $longitude = input('param.longitude');

        //一级分类nid
        $nidone = empty(input('param.nidone')) ? '' : input('param.nidone');

        //二级分类nid
        $nidtwo = empty(input('param.nidtwo')) ? '' : input('param.nidtwo');

        $citycode = input('param.citycode');

        //区域id
        $countycode = input('param.countycode/a');
        $countycode = count($countycode)>0 ? $countycode : '';

        //性别要求 0：不限 1：男 2：女
        $sexCond = empty(input('param.sexCond')) ? 0 :input('param.sexCond');

        //其他条件 0选择 1没选择
        $otherCond = empty(input('param.otherCond')) ? 0 : input('param.otherCond');



        //测试数据
        // $user_id = 1;
        // $sort = 1;

        // $latitude = '24.98695';
        // $longitude = '118.88637';

        // $nidone = '';
        // $nidtwo = '';

        // $citycode = 350200;
        // $countycode = [];
        // $sexCond = 0;
        // $otherCond = 1;



        //当前页码,默认为第一页
        $pageNum = empty(input('param.pageNum')) ? 1 : input('param.pageNum');
        //每页数量
        $numPerPage = 8;

        if(empty($user_id)){
            $result['code'] = 2;
            $result['msg'] = '暂无用户信息';
            return json($result);
        }

        //初始化查询条件
        $where['is_del'] = 0;
        //只有1级分类不为空，才能有二级分类
        if(!empty($nidone)){
            if(!empty($nidtwo)){
                $where['one_fnid|two_fnid|three_fnid'] = $nidone;
                $where['one_nid|two_nid|three_nid'] = $nidtwo;
            }else{
                $where['one_fnid|two_fnid|three_fnid'] = $nidone;
            }
        }

        //限制性别
        if(!empty($sexCond)){
            $where['sex'] = $sexCond == 1 ? ['in','0,1'] : ['in','0,2'];
        }

        //其他条件查询
        if(empty($otherCond)){
            $a = 1;
            $where[] = ['exp',Db::raw("find_in_set($a,other_condition)")];
        }else{
            $a = 1;
            $where[] = ['exp',Db::raw("!find_in_set($a,other_condition)")];
        }
        $a = 1;
        $where[] = ['exp',Db::raw("locate($a,addip) != 0")];

        if(!empty($countycode)){
            $where['threecode'] = ['in',$countycode];
        }else{
            $where['twocode'] = ['in',$citycode];
        }

        //起始数据
        $index = ($pageNum-1)*$numPerPage;

        if($sort != 1){
            if($sort == 0 || $sort == -1){
                $orderArr['orders'] = 'desc';
            }elseif($sort == 2){
                $orderArr['addtime'] = 'desc';
            }elseif($sort == 3){
                $orderArr['is_urgent'] = 'desc';
            }

            $now = Db::table('work')->where($where)->order($orderArr)->field('addip,addtime,work_detail,is_del,orders,one_fnid,two_fnid,three_fnid,one_nid,two_fnid,three_fnid',true)->limit($index,$numPerPage)->select();
            //echo Db::getLastSql() . "<br/>";
        }else{
            if(empty($latitude) || empty($longitude)){
                $result['code'] = 2;
                $result['msg'] = '当前位置未确定！';
                return json($result);
            }
            $now = Db::table('work')->where($where)->field("id,title,need_nums,detailaddress,onecode,twocode,threecode,province,city,county,latitude,longitude,sex,pay_type,work_time,cost_type,cost,is_urgent,page_view,other_condition,round(6378.138*2*asin(sqrt(pow(sin((latitude*pi()/180-$latitude*pi()/180)/2),2)+cos(latitude*pi()/180)*cos($latitude*pi()/180)* pow(sin((longitude*pi()/180-$longitude*pi()/180)/2),2)))*1000) distance")->order('distance asc')->limit($index,$numPerPage)->select();
            //echo Db::getLastSql() . "<br/>";
        }
        $total = Db::table('work')->where($where)->count('id');


        $nowTotal = count($now);
        if($nowTotal>0){
            foreach ($now as $k=>$v){
                $danwei = $v['cost_type'] == 0 ? '小时' : ($v['cost_type'] == 1 ? '其他' : '月');
                $now[$k]['newPrice'] = "{$v['cost']}元/{$danwei}";
            }
        }

        $result['total'] = $total;
        $alreadyLoad = ($pageNum-1)*$numPerPage + $nowTotal;
        if($nowTotal>0){
            if($total-$alreadyLoad<=0){
                $result['havenums'] = 0;
            }else{
                $result['havenums'] = $total-$alreadyLoad;
            }
            $result['workList'] = $now;
            $result['code'] = 1;
        }else{
            $result['workList'] = [];
            $result['code'] = 2;
        }
        return json($result);
    }