laravel5+复杂原生sql编写多条件查询定位经纬度距离排序及paginate分页
管理员 发布于 4年前 404
描述：laravel链式操作虽然方便那是针对普通业务，稍微复杂点的我感觉还是原生sql来的实在
话不多说分享一下一个案例：
多条件查询，定位经纬度计算距离附近排序，分页
$where = request()->input();
if (!isset($where['lng']) || !isset($where['lat'])) {return '未传经纬度';}
try {
$sql = "SELECT
lara_90_cbb_palmlife.id,
lara_90_cbb_palmlife.tid,
lara_90_cbb_palmlife.city,
lara_90_cbb_palmlife.area,
lara_90_cbb_palmlife.business,
lara_90_cbb_palmlife.type_name,
lara_90_cbb_palmlife.img,
lara_90_cbb_palmlife.title,
lara_90_cbb_palmlife.lat,
lara_90_cbb_palmlife.lng,
lara_90_cbb_palmlife.youhui,
round(st_distance_sphere(point(lara_90_cbb_palmlife.lng,lara_90_cbb_palmlife.lat),point({$where['lng']},{$where['lat']}))/1000,2) as space
FROM
`lara_90_cbb_palmlife`
LEFT JOIN `lara_92_cbb_palmlife_coupon_relation` ON `lara_90_cbb_palmlife`.`tid` = `lara_92_cbb_palmlife_coupon_relation`.`palmlife_id`
LEFT JOIN `lara_91_cbb_palmlife_coupon` ON `lara_91_cbb_palmlife_coupon`.`cid` = `lara_92_cbb_palmlife_coupon_relation`.`palmlife_coupon_id`
WHERE ";
if (isset($where['city'])) {
$sql .= "city = '{$where['city']}'";
}
if (isset($where['area'])) {
$sql .= " and area = '{$where['area']}'";
}
if (isset($where['business'])) {
$sql .= " and business = '{$where['business']}'";
}
if (isset($where['type'])) {
$sql .= " and type_name = '{$where['type']}'";
}
if (isset($where['bank'])) {
$sql .= " and lara_90_cbb_palmlife.bank = {$where['bank']}";
}
if (isset($where['bank'])) {//查优惠表
$sql .= " and (lara_91_cbb_palmlife_coupon.bank = {$where['bank']} or lara_91_cbb_palmlife_coupon.bank is NULL)";
}
if (isset($where['week'])) {//查优惠表
$sql .= " and (youhui LIKE '%周一至周日%' OR lara_91_cbb_palmlife_coupon.title LIKE '%{$where['week']}%' )";
}
if (isset($where['kw'])) {
$sql .= " and lara_90_cbb_palmlife.title like '%{$where['kw']}%'";
}
$sql .= "GROUP BY lara_90_cbb_palmlife.tid";
if (isset($where['space'])) {//1000千米内距离排序 根据定位跟经纬度计算距离
$sql .= " HAVING space < {$where['space']} ORDER BY space ASC , lara_90_cbb_palmlife.id" ;
}else{
$sql .= " ORDER BY space ASC , lara_90_cbb_palmlife.id";
}
//$sql .= " LIMIT 100";
//$res = DB::select($sql);
$sql = '('.$sql.') cc';//定义临时表
$res = DB::table(DB::raw($sql))->paginate(10);
return response()->json(array('status' => 200, 'msg' => '成功', 'res' => $res));
} catch (Exception $e) {
return response()->json(array('status' => 201, 'msg' => '失败'));
}
请勿发布不友善或者负能量的内容。与人为善，比聪明更重要！
该博客于2020-12-7日，后端基于go语言的beego框架开发
前端页面使用Bootstrap可视化布局系统自动生成
是我仿的原来我的TP5框架写的博客，比较粗糙，底下是入口
侯体宗的博客
文章标签
- test 三种扣减库存方案 Hyperf2.1启动报错端口占用 上外网神器Ghelper 上外网神器集装箱 laravel中HMVC模式 Hyperf框架验证器 Hyperf自定义函数编写 Hyperf对象转成数组 PHP2021面试题集 centos7系统上挂载 Hyperf框架 秒杀系统的设计 Laravel Octane PHP数组底层实现原理 php7垃圾回收机制 Laravel中类型转换 mysql事务特征及隔离级别 Linux-awk命令 mysql编码 JWT源码实现 Laravel内核分析 Laravel中的用法 centos7+go环境+beego框架 go+beego框架开发博客 go+beego开发博客首页 go+beego自定义公共函数 beego注册_登录_查询 beego控制器创建 Beego框架安装 go语言环境安装 laravel+Elasticsearc laravel运行原理 windows7搭建Vuejs环境 Laravel分块 laravel去重(distinct() laravel集合 laravel+mysql读写分离 laravel5+复杂原生sql laravel+MissMyCat/al lnmp sessio laravel自定义数组分页 laravel中.env配置 laravel8队列发邮件 码云仓库常用操作 laravel8配置163SMTP laravel8配置全局函数 laravel/jetstream之认证 laravel/jetstream
友情链接
