Multilanguage with default value. Is there another solution?

Question

SQL

The site must support multilingualism, I chose the following solution for myself: a table that requires translations has an additional. table prefixed with _i18n, they store common values ​​that must be in multiple languages. But I ran into a problem that I can not solve with a single request. It is important for the client that the information is displayed in the default language, if it is not filled in the language chosen by the user. Search to work similarly, if the value is filled, searches for it, if not, then in the default language.
So that the data always comes out, regardless of the translation, I wrote the following query (for example):

SELECT
    region.*,
    COALESCE(sLang.name, dLang.name) as name
FROM
    region
LEFT JOIN region_i18n AS sLang ON sLang.region_id = region.id AND slang.lang_id = 2
LEFT JOIN region_i18n AS dLang ON dLang.region_id = region.id AND dLang.lang_id = 1

And everything is displayed correctly, even if the name of the area does not have a translation, it is displayed in the default language. But what about the search for such a request? For almost all requests, I always had enough where, here it does not want to work. When I make a request with WHERE name LIKE '%Крас%', it writes an error that such a column does not exist. I found a way out for filtering through HAVING name LIKE '%Крас%',then everything is searched normally. Will there be any problems with a large number of data with such queries? Plus, at the moment I'm using COALESCE(sLang.name, dLang.name) as nameto pull data in the first language that has a value. It turns out for each translatable field, I need to use it. In some tables there may be 5-10 such fields, will such a selection greatly affect performance?

Answer 1

WHERE and HAVING are not the same thing. that is why the name alias is not available in the first case and is available in the second. but to solve the problem with such a crutch - to hit hard on performance.
though it will be slow anyway. like on a calculated field, always past the indexes. no good.