PHP TP5 省市区表多级关联模型查询，和db方式查询封装效率

      需要对省市区 三张表进行查询，将查询结果 进行组装成如下格式,便于前段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：

<?phpnamespace app\common\model;use think\Model;use think\Db;class Province extends Model{    public function cities()    {        return $this->hasMany('City','province')->order('id');    }}

City：

<?phpnamespace app\common\model;use think\Model;class City extends Model{    public function areas()    {        return $this->hasMany('Area','city','id')->order('id');    }}

Area：

<?phpnamespace app\common\model;use think\Model;class Area extends Model{}

2. 在ProvinceSercice中，采用三种方式来实现数据查询和封装

<?phpnamespace app\common\service;use think\Db;abstract class ProvinceService{    /**     * 获取省市区 Array     * @return ModelList     */    public static function getProvinceJSONstr()    {        $provinceModel =new \app\common\model\Province();        $provinceList = $provinceModel->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中三种方式的效率

<?phpnamespace app\index\controller;use think\Controller;class Index extends Controller{    public function index()    {        //程序运行时间        $starttime = explode(' ',microtime());        echo microtime();        //$md = 'getProvinceJSONStr';        //$md = 'getProvinceJSONStr2';        $md = 'getProvinceJSONStr3';        $provinces = \app\common\service\ProvinceService::$md();        $provinces = \app\common\service\ProvinceService::$md();        $provinces = \app\common\service\ProvinceService::$md();        $provinces = \app\common\service\ProvinceService::$md();        $provinces = \app\common\service\ProvinceService::$md();        dump($provinces);        //程序运行时间        $endtime = explode(' ',microtime());        $thistime = $endtime[0]+$endtime[1]-($starttime[0]+$starttime[1]);        $thistime = round($thistime,6);        echo "本网页执行耗时：".$thistime." 秒。".time();    }}

将ProvinceSercice中，1.关联模型查询  2.Db执行sql关联查询 3.sql普通查询，对结果手动封装

这三个方法 都执行 5 次，打印结果，计算执行时间（）

 方法1：本网页执行耗时：1.934399 秒；（左右）

 方法2：本网页执行耗时：0.190413 秒；（左右）

 方法3：本网页执行耗时：0.135423秒；（左右）

可以发现 ：方法3,的执行效率最高！