laravel model

        $query = DB::table(self::$_table)
                ->when(!empty($params), function($query) use ($params){
                    foreach ($params as $column=>$val){
                        $query->where($column, $val);
                    }
                })
                ->where('del_status', Constant::DATA_IS_NORMAL);
        return (array)$query->first(['*']);

Union All :

        $fields = ['uuid','title','end_time','end_time_range','city_sys_no','city_name','interested_cnt','created_at','updated_at'];
        $where = [
            ['end_time','>=', date('Y-m-d')],
            ['del_status' , Constant::DATA_IS_NORMAL],
            [self::TABLE_FIELD_PUBLISH , Constant::IS_ENABLED],
        ];
        $query1 = DB::table('activity_info')->select($fields)->where($where)->orderBy('end_time','asc')->limit(1000);

        $where = [
            ['end_time','<', date('Y-m-d')],
            ['del_status' , Constant::DATA_IS_NORMAL],
            [self::TABLE_FIELD_PUBLISH , Constant::IS_ENABLED],
        ];
        $query2 = DB::table('activity_info')->select($fields)->where($where)->orderBy('end_time','desc')->limit(1000);

        $mergedQuery = $query1->unionAll($query2);

        //$results = $mergedQuery->get(); //查询全部记录数据(不需要分页时的处理)

        //分页处理
        $size = $param['limit'] ?? BaseModel::getPageSize();
        $page = $mergedQuery->paginate($size, $fields,'page');
        $total = $page->total();  //总记录数量
        $results = $page->items(); //分页后的查询数据列表

 

　　

    //原生sql查询（附近地图）
    public function  getListByOri($param){
            $result = ['list'=>[], 'count'=>0];
            $number = $param['page'];
            $size = $param['limit'] ?? 1000;

            $lon = $param['lon']; //经度
            $lat = $param['lat'];//纬度

            $sql = <<<EOD
                SELECT %s FROM na_clinic_information 
                EOD;

            $fields = "count(id) cnt";
            $query = sprintf($sql, $fields);

            $whereStr = " WHERE is_enabled=1 ";
            //判断查询条件
            if(!empty($param['city_name'])){
                $city_name = $param['city_name'];
                $whereStr = $whereStr." AND city_name='{$city_name}'";
            }
            if(!empty($param['province_name'])){
                $province_name = $param['province_name'];
                $whereStr = $whereStr." AND province_name='{$province_name}' ";
            }

            $resultTotal = (array)DB::selectOne($query.$whereStr);

            $earthRadius = 6371;
            if($resultTotal['cnt']>0){
                $fields = "id, clinic_name, address_detail,province_name,city_name,phone_number,lon,lat,clinic_type,
                        ({$earthRadius} * 2 * ASIN(SQRT(
                            POWER(SIN(({$lat} - ABS(lat)) * PI() / 180 / 2), 2) +
                            COS({$lat} * PI() / 180) *
                            COS(ABS(lat) * PI() / 180) *
                            POWER(SIN(({$lon} - lon) * PI() / 180 / 2), 2)
                        ))) AS distance";

                $sql .= $whereStr;
                $sql .= " ORDER BY distance ";
                $start = ($number-1)*$size;
                $limitStr = " limit {$start},{$size}";
                $query = sprintf($sql, $fields).$limitStr;
                $resultList = DB::select($query);
                $resultList = Common::objectToArray($resultList);
                $result['list'] = $resultList;
                $result['count'] = $resultTotal['cnt'];
            }

            return $result;
    }