Querying for a specific language and if not found use default one instead

Posted on

Question :

I currently store all language data (including default language) in my database. Generally a simple query making the assumption the language exists is as simple as doing:

SELECT a.artifact_id, 
FROM GameArtifact a 
JOIN GameArtifactNameDesc b 
    ON a.artifact_id = b.artifact_id AND b.locale = 'en-US'
JOIN GameClassTranslation c 
    ON c.identifier = a."exclusive" AND c.locale = 'en-US';

And a result from that said query:

-[ RECORD 1 ]-----+-------------------------------------------------------------------
artifact_id       | abyssal-crown
base_atk          | 15
base_hp           | 54
max_atk           | 195
max_hp            | 702
name              | Abyssal Crown
class             | Mage
lore_description  | May those ruled by the crown walk the road of corruption forever. +
                  | - Witch Reinwood, hung high up on the stake
skill_description | 12.0% (24.0%) chance to stun the enemy for 1 turn after attacking.

The issue is, what if the language (which is set on the application) is not found? How can I set it to use a default language (let’s say: es-AR) and query for that language? Or what if there’s translation for that language in one table but not in both?

I thought of two approaches:

Approach #1

Doing the query presented above and if it doesn’t return anything, query again using the default language which I can be certain there’s data of in the database.

This doesn’t sound convincing and nice to do.

Approach #2

Cut off the short tables and do their translations on the application. Which in this case GameClassTranslation, which only contains 49 rows, 7 languages so 7 rows per language but which might expand in the future.

This also doesn’t sound convincing from the point of keeping everything in one place rather than split everywhere.

Is there any better solution for this?

Answer :

Giving next example:

create table a (id int, description text);
create table b (id int, lang text, description text, default_lang int);
insert into a values (1, 'Yo vivo en Barcelona');
insert into b values 
(1, 'en-US', 'I am living in Barcelona', 0), 
(1, 'es-AR', 'Yo vivo en Barcelona', 1);

If you table have a default text for the default language you could use a LEFT JOIN and COALESCE in this way:

    coalesce(b.description, a.description) as description
left join
    on b.id = a.id and b.lang = 'en-US';
id | description             
-: | :-----------------------
 1 | I am living in Barcelona

Or you could add a default_lang column in translations table and use a LATERAL JOIN with LIMIT 1:

    coalesce(b.description, a.description) as description
join lateral
    (select id, description
     from   b
     where  lang = 'en-EN' or lang = 'es-AR'
     order by default_lang
     limit 1) b on true 
id | description         
-: | :-------------------
 1 | Yo vivo en Barcelona

db<>fiddle here

Leave a Reply

Your email address will not be published. Required fields are marked *