How to get rid of unnecessary queries to the database when creating custom URL rules in Yii2?

Question

Yii

I created my own rule for generating a URL in Yii2, the problem is that when displaying a link like:

<a href="<?= Url::to(['site/test', 'country' => 'de'])?>">Test Route</a>
<a href="<?= Url::to(['site/test', 'country' => 'de'])?>">Test Route</a>
<a href="<?= Url::to(['site/test', 'country' => 'fr'])?>">Test Route</a>
<a href="<?= Url::to(['site/test', 'country' => 'ru'])?>">Test Route</a>

At the same time, duplicate queries to the database of the form are created: Only queries with id=DE were created: 6 judging by Yii Debug, how can I get rid of this, reduce it?
SELECT * FROM `countries` WHERE `id`='DE' LIMIT 1

<?php

namespace components;

use common\models\City;
use common\models\Country;
use yii\base\Object;
use yii\web\UrlRuleInterface;

class CountryCityUrlRule extends Object implements UrlRuleInterface
{

    public function createUrl($manager, $route, $params)
    {
        if ($route === 'site/test'){

            if ( isset($params['country']) ){

                $country = Country::find()->select('name')->asArray()->where(['id' => $params['country']])->limit(1)->scalar();

                if ( isset($params['city']) ){
                    $city = City::find()->select('name_en')->asArray()->where(['id' => $params['city'], 'country_id' => $country])->limit(1)->scalar();

                    if ($country !== false && $city !== false){
                        return $country . '/' .  $city;
                    }
                }

                if ($country !== false) {
                    return $country;
                }

            }

        }
        return false;
    }

    public function parseRequest($manager, $request)
    {
        $pathInfo = $request->getPathInfo();

        if (preg_match('%^(\w+)(/(\w+))?$%', $pathInfo, $matches)) {

            if (isset($matches[1])){
                $country_id = Country::find()->select('id')->asArray()->where(['name' => $matches[1]])->limit(1)->scalar();

                if (isset($matches[3])){

                    $city_id = City::find()->select('id')->asArray()->where(['name_en' => $matches[3], 'country_id'=>$country_id])->limit(1)->scalar();

                    if ($country_id !== false && $city_id !== false){
                        return ['site/test', ['country' => $country_id, 'city'=>$city_id]];
                    }
                }

                if ($country_id !== false) {
                    return ['site/test', ['country' => $country_id]];
                }
            }



        }
        return false;
    }

}

Answer 1

Fasten the caching of the result of the SQL query.
www.yiiframework.com/doc-2.0/guide-caching-data.html
Example:

$cacheDuration = 60; // 1 min
$country_id = Country::getDb()->cache(function ($db) {
    return Country::find()->select('id')->asArray()->where(['name' => $matches[1]])->limit(1)->scalar();
}, $cacheDuration);

Answer 2

I decided in the following way, first I reduced the entire selection to one request, then I worked on the array, + I also took your advice, thanks ...

private function getCountry($countryId = null){

        if (isset($countryId)){

            $result = Country::getDb()->cache(function ($db) {

                return Country::find()->select('id, name')->asArray()->indexBy('id')->all();

            });

            return $result[$countryId]['name'];
        }
        return null;

    }