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

localizationpostgresqlquery

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, 
       a.base_atk, 
       a.base_hp, 
       a.max_atk, 
       a.max_hp, 
       b.name, 
       c."class", 
       b.lore_description, 
       b.skill_description 
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?

Best 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:

select
    a.id,
    coalesce(b.description, a.description) as description
from
    a
left join
    b
    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:

select
    a.id,
    coalesce(b.description, a.description) as description
from
    a
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

Related Solutions

SQL Server 2008 – How to Get Text for Default Language

Using a recursive CTE, this query builds a table of all languages that point somewhere in their default chain to a language that has the required comment. It shows each language next to its first default that has the required comment. It then filters this table to give you the first available text for the language you selected.

DECLARE @CommentId BIGINT = 1;
DECLARE @LanguageCode NVARCHAR(2) = 'en';

WITH languages AS (
    -- base case: language has required comment
    SELECT 
          lt.LanguageId AS RootLanguageId
        , lt.LanguageId
        , lt.Code
        , lt.DefaultId
        , 0 AS Level
    FROM 
        dbo.common_LanguageType lt
    --WHERE
    --  lt.LanguageId = lt.DefaultId
    WHERE
        EXISTS (
            SELECT *
            FROM dbo.common_Text    t
            WHERE 
                    t.CommentId = @CommentId
                AND t.LanguageId = lt.LanguageId
        )

    UNION ALL

    -- recursive case: language is not its own default and
    --                 does not have the required comment
    SELECT
          l_default.RootLanguageId
        , l.LanguageId
        , l.Code
        , l.DefaultId
        , l_default.Level + 1 AS Level
    FROM
                    dbo.common_LanguageType l
        INNER JOIN  languages               l_default
            ON l.DefaultId = l_default.LanguageId
    WHERE
            l.LanguageId <> l.DefaultId
        AND NOT EXISTS (
            SELECT *
            FROM dbo.common_Text    t
            WHERE 
                    t.CommentId = @CommentId
                AND t.LanguageId = l.LanguageId
        )
)
SELECT t.Text
FROM 
                languages       l
    INNER JOIN  dbo.common_Text t
        ON  l.RootLanguageId = t.LanguageId
WHERE
    l.Code = @LanguageCode
;

Try just running

SELECT *
FROM languages;

if you want to get a feel for what the recursive CTE is doing. I've uploaded a script to create the tables, insert some sample data, and run the above code to gist.

Pretty sexy, no?

SQL Query – How to Select Distinct for One Column and Any in Another Column

The easiest query to write is for MySQL (with not strict ANSI settings). It uses the non-standard construction:

SELECT key, value
FROM tableX
GROUP BY key ;

In recent version (5.7 and 8.0+) where the strict settings and ONLY_FULL_GROUP_BY are the default, you can use the ANY_VALUE() function, added in 5.7:

SELECT key, ANY_VALUE(value) AS value
FROM tableX
GROUP BY key ;

For other DBMSs, that have window functions (like Postgres, SQL-Server, Oracle, DB2), you can use them like this. The advantage is that you can select other columns in the result as well (besides the key and value) :

SELECT key, value
FROM tableX
    ( SELECT key, value,
             ROW_NUMBER() OVER (PARTITION BY key 
                                ORDER BY whatever)     --- ORDER BY NULL
               AS rn                                   --- for example
      FROM tableX
    ) tmp 
WHERE rn = 1 ;

For older versions of the above and for any other DBMS, a general way that works almost everywhere. One disadvantage is that you cannot select other columns with this approach. Another is that aggregate functions like MIN() and MAX() do not work with some datatypes in some DBMSs (like bit, text, blobs):

SELECT key, MIN(value) AS value
FROM tableX
GROUP BY key ;

PostgreSQL has a special non-standard DISTINCT ON operator that can also be used. The optional ORDER BY is for selecting which row from every group should be selected:

SELECT DISTINCT ON (key) key, value
FROM tableX
-- ORDER BY key, <some_other_expressions> ;

Best Answer

Related Solutions

SQL Server 2008 – How to Get Text for Default Language

SQL Query – How to Select Distinct for One Column and Any in Another Column

Related Question