laravel5+复杂原生sql编写多条件查询定位经纬度距离排序及paginate分页

管理员 发布于 3年前   368

描述: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框架写的博客,比较粗糙,底下是入口
侯体宗的博客

      订阅博客周刊

文章标签

友情链接

HouTiZong
侯体宗的博客
© 2020 zongscan.com
版权所有ICP证 : 粤ICP备20027696号
PHP交流群
侯体宗的博客