PHP TP5 Provinces and Cities Table Multilevel association model query, and DB mode query encapsulation efficiency ____php

Need to the provincial urban area three tables to query, the query results are assembled into the following format, to facilitate the preceding paragraph JS call

Array (size=34)
  1 => 
    Array (size=4)
      ' id ' => int 1
      ' name ' => string ' Beijing ' (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 ' Tianjin ' (length=9)
      ' code ' => string ' 1200 ' (length=6)
      ' cities ' => 
        Array (size=2)
          3 => 
            Array (size=5)
              ...
          4 => 
            Array (size=5) ...
     ...

Using the TP5 framework
Implementation code:

1. Model: Establishing an association

Province:

<?php
namespace App\common\model;
Use Think\model;
Use think\db;
Class Province extends Model
{public
    function cities ()
    {return
        $this->hasmany (' City ', ' Province ' )->order (' id ');
    }

City:

<?php
namespace App\common\model;
Use Think\model;
Class City extends Model
{public
    function areas ()
    {return
        $this->hasmany ("area", ' City ', ' ID ') )->order (' id ');
    }

Area:

<?php
namespace App\common\model;
Use Think\model;
Class Area extends Model
{

}

2. In Provincesercice, three ways to implement data query and encapsulation

<?php namespace App\common\service;
Use think\db; Abstract class Provinceservice {/** * Capture the provinces and cities Array * @return modellist/public static function ge
        Tprovincejsonstr () {$provinceModel =new \app\common\model\province ();
        $provinceList = $provinceModel->with (' Cities.areas ')->select ();
    return $provinceList;
        /** * Get the Provinces and cities array * @return Array * * * public static function getProvinceJSONStr2 () { ================= provinces and cities to obtain ===========//reduce unnecessary SQL queries, using the DB approach rather than using the association model $provinceList = db::name (' Provin Ce ')->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_cod E, 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; //================= provinces and cities to obtain ===========//reduce unnecessary SQL queries, using the DB approach rather than using the association model public static function Getprovinc
        EJSONSTR3 () {$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. Execute in controller, calculate the efficiency of three ways in service

<?php
namespace App\index\controller;
Use Think\controller;
Class Index extends Controller
{public
    Function index ()
    {
        //program run time
        $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);
        Program Running time
        $endtime = explode (', microtime ());
        $thistime = $endtime [0]+ $endtime [1]-($starttime [0]+ $starttime [1]);
        $thistime = Round ($thistime, 6);
        echo "This page is time-consuming to perform:". $thistime. "Seconds. ". Time ();
    }
}

Will Provincesercice, 1. Association model query 2.DB Execute SQL Association query 3.sql Normal query, manual encapsulation of results

These three methods are performed 5 times, print the results, calculate the execution time ()

Method 1: This Web page execution time is time-consuming: 1.934399 seconds;

Method 2: This Web page execution time is time-consuming: 0.190413 seconds;

Method 3: This Web page execution time is time-consuming: 0.135423 seconds;

Can be found: Method 3, the most efficient execution.