本文共 3411 字,大约阅读时间需要 11 分钟。
需要对省市区 三张表进行查询,将查询结果 进行组装成如下格式,便于前段js调用
array (size=34) 1 => array (size=4) 'id' => int 1 'name' => string '北京市' (length=9) 'code' => string '110000' (length=6) 'cities' => array (size=2) 1 => array (size=5) ... 2 => array (size=5) ... 2 => array (size=4) 'id' => int 2 'name' => string '天津市' (length=9) 'code' => string '120000' (length=6) 'cities' => array (size=2) 3 => array (size=5) ... 4 => array (size=5) ... ...
使用TP5框架 实现代码:
1. model:建立关联
Province:
hasMany('City','province')->order('id'); }}City:
hasMany('Area','city','id')->order('id'); }}
Area:
2. 在ProvinceSercice中,采用三种方式来实现数据查询和封装
with('cities.areas')->select(); return $provinceList; } /** * 获取省市区 Array * @return Array */ public static function getProvinceJSONStr2() { //================= 省市区数据获取 =========== // 减少不必要的sql查询,特采用db方式查询 而非采用关联模型 $provinceList = Db::name('province') ->alias('p') ->join('city c','p.id = c.province','LEFT') ->join('area a','c.id = a.city','LEFT') ->field('p.id,p.name,p.code,c.id as city_id,c.name as city_name,c.code as city_code, a.id as area_id,a.name as area_name,a.code as area_code') ->order(['p.id','c.id','a.id']) ->select(); $provinceArray = []; foreach($provinceList as $val) { $provinceArray[$val['id']]['code'] = $val['code']; $provinceArray[$val['id']]['name'] = $val['name']; $provinceArray[$val['id']]['cities'][$val['city_id']]['city_code'] = $val['city_code']; $provinceArray[$val['id']]['cities'][$val['city_id']]['city_name'] = $val['city_name']; $provinceArray[$val['id']]['cities'][$val['city_id']]['areas'][$val['area_id']] = [ 'area_name' => $val['area_name'], 'area_code' => $val['area_code'] ]; } return $provinceArray; } //================= 省市区数据获取 =========== // 减少不必要的sql查询,特采用db方式查询 而非采用关联模型 public static function getProvinceJSONStr3() { $provincelist = Db::name('province') ->order('id') ->select(); $citylist = Db::name('city') ->order('id') ->select(); $arealist = Db::name('area') ->order('id') ->select(); $provinceArray = []; $cityArray = []; $areaArray = []; foreach($arealist as $area) { $areaArray[$area['city']][$area['id']] = $area; } foreach($citylist as $city) { $city['areas'] = isset($areaArray[$city['id']]) ? $areaArray[$city['id']] : null; $cityArray[$city['province']][$city['id']] = $city; } foreach($provincelist as $province) { $province['cities'] = isset($cityArray[$province['id']]) ? $cityArray[$province['id']] : null; $provinceArray[$province['id']]= $province; } return $provinceArray; }}
3.在Controller中执行,计算service中三种方式的效率
将 ProvinceSercice中,1.关联模型查询 2.Db执行sql关联查询 3.sql普通查询,对结果手动封装
这三个方法 都执行 5 次,打印结果,计算执行时间()
方法1:本网页执行耗时:1.934399 秒;(左右)
方法2:本网页执行耗时:0.190413 秒;(左右)
方法3:本网页执行耗时:0.135423秒;(左右)
可以发现 :方法3,的执行效率最高!
转载地址:http://delii.baihongyu.com/