User - Profile - Country Field without value

[luke-]

• Create a "Country" profile field type
• Select a Country
• Go to About Page, the Country is empty

[yurabakhtin]

@luke- Fixed in PR #6937, I think it was done here by mistake because I don't think we should store in DB full country name like "Germany" instead of previous way where we stored only a country code DE.

[luke-]

@yurabakhtin We should only store the country code in the database and display the localized country name (if possible).
Is that not the case? If so, we should reconsider.

One problem, when storing the code, could be the search for a country name.

[yurabakhtin]

@luke-

We should only store the country code in the database and display the localized country name (if possible).
Is that not the case? If so, we should reconsider.

Yes, after my current fix we store in DB only a country code, and it worked this way before the changes from the PR #6809.
I think it was a mistake to use a key in the array as country full name:
$items[Iso3166Codes::country($code)] = Iso3166Codes::country($code);

One problem, when storing the code, could be the search for a country name.

Yes, it is a problem, because there is a simple SQL query like profile.country LIKE 'keyword', i.e. if we store only DE then we cannot find a user by keyword Germany.

[yurabakhtin]

@luke- However we cannot store a full country name in DB because the names are translatable.

Let me know if I should try to implement a solution like this:

$countryCodes = [$keyword];

foreach (Iso3166Codes::$countriesas $code => $value) {
    if (stripos(Iso3166Codes::country($code), $keyword) !== false) {
        $countryCodes[] = $code;
    }
}

$query->andWhere(['IN', 'profile.country', $countryCodes]);

[luke-]

@yurabakhtin Ok perfect. I just wanted to make sure that we really only save the country code. Then that's fine.

Would be cool if you could try this wordaround, then we would have solved the search issue.

[yurabakhtin]

Would be cool if you could try this wordaround, then we would have solved the search issue.

@luke- Done in the commit 11c2f3f.

[marc-farre]

@yurabakhtin Thanks for the fix.
Is there a migration planned to convert full country names to ISO 3166 codes in the DB (for profiles created or updated during the bug)?

[yurabakhtin]

Is there a migration planned to convert full country names to ISO 3166 codes in the DB (for profiles created or updated during the bug)?

@marc-farre Good question! I see a full country name was stored in DB from Iso3166Codes::country($code, $translate = true), i.e. a country name was stored as translated, so I am afraid it may be stored as "France" and "Frankreich" and "Francia" and etc. I don't think it will be a good idea to run a search process for a country code between 250 countries and 47 languages.

[marc-farre]

@yurabakhtin Maybe we could do a migration for the default language only? If not, as I have to do it for some instances I manage, I'll share the code here.

[yurabakhtin]

@marc-farre Ideally we should do some update in a migration by single query like UPDATE profile SET country = $correctCode WHERE country = $wrongCode, but it is impossible for current case, so we could try a solution like this:

$profiles = Profile::find()
    ->select('country')
    ->distinct('country')
    ->where(['NOT IN', 'country', array_keys(Iso3166Codes::$countries)])
    ->andWhere(['IS NOT', 'country', new Expression('NULL')]);

foreach ($profiles->column() as $wrongCountryCode) {
    Profile::updateAll(
        ['country' => $this->getCodeByCountry($wrongCountryCode)],
        ['country' => $wrongCountryCode]);
}

public function getCodeByCountry($wrongCountryCode): ?string
{
    if ($this->translatedCountries === null) {
        $this->translatedCountries = [];
        foreach (Iso3166Codes::$countries as $code => $title) {
            $this->translatedCountries[$code] = [];
            foreach (Yii::$app->params['availableLanguages'] as $language => $name) {
                $this->translatedCountries[$code][] = \Locale::getDisplayRegion('_' . $code, $language);
            }
        }
    }

    foreach ($this->translatedCountries as $code => $titles) {
        if (in_array($wrongCountryCode, $titles)) {
            return $code;
        }
    }

    return null;
}

So the array $this->translatedCountries will have 251 items and which item is an array with 48 translated county titles like this:

The array $profiles->column() may have max size 251*48 = 12048, but I am not sure it may be so much, because firstly a county title can be translated same for several languages and I don't think all they be touched during the period when it was broken, so it seems we could apply such migration.

@luke- Do you agree?

[marc-farre]

@yurabakhtin Thanks very much! Tested, it works fine.

[luke-]

@yurabakhtin Looks good for me. Should be a good approach which also scales...

[yurabakhtin]

@marc-farre Have you already started a PR for the migration code or should I do this?

[marc-farre]

@luke- @yurabakhtin PR #6956