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)
              ...
     ...

1. model：建立关联

Province：

<?php
namespace appcommonmodel;
use thinkModel;
use thinkDb;
class Province extends Model
{
    public function cities()
    {
        return $this->hasMany("City","province")->order("id");
    }
}

<?php
namespace appcommonmodel;
use thinkModel;
class City extends Model
{
    public function areas()
    {
        return $this->hasMany("Area","city","id")->order("id");
    }
}

Area：

<?php
namespace appcommonmodel;
use thinkModel;
class Area extends Model
{

}

<?php
namespace appcommonservice;
use thinkDb;
abstract class ProvinceService
{
    /**
     * 获取省市区 Array
     * @return ModelList
     */
    public static function getProvinceJSONstr()
    {
        $provinceModel =new appcommonmodelProvince();
        $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中三种方式的效率

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

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

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

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

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

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