Retrieving i18n data with fallback language

Question

I have to grab i18n text from a database. The default language is English, it has text for everything. But the non-English languages doesn't necessarily have all the desired translations. If a non-English translation for a certain entity/key isn't available in the DB, then I'd like to have it to return the English text instead. So, English is the fallback language here.

The i18n text table look like so (PostgreSQL dialect):

CREATE TABLE translation (
    id SERIAL PRIMARY KEY,
    language_code CHAR(2) NOT NULL,
    key VARCHAR(20) NOT NULL,
    value TEXT NOT NULL,
    CONSTRAINT translation_unique UNIQUE (language_code, key)
)

The data look like this:

INSERT INTO translation 
    (language_code, key, value) 
VALUES
    ('en', 'foo', 'foo in English'),
    ('nl', 'foo', 'foo in Nederlands (Dutch)'),
    ('en', 'bar', 'bar in English')

I'd like to basically do a :

SELECT key, value 
FROM translation 
WHERE (language_code = 'nl' OR IF value IS NULL THEN language_code = 'en')

(actually, the 'nl' value is to be parameterized)

So that it returns the following:

+-----+---------------------------+
| key | value                     |
+-----+---------------------------+
| foo | foo in Nederlands (Dutch) |
| bar | bar in English            |
+-----+---------------------------+

How can I do it the best in a clean, consice, self-explaining and efficient SQL way? I don't have a strong SQL-fu and all my attempts as far leads to multiple (2~3) queries or duplicate rows in the result when attempting in a single query (values are returned in both the desired and fallback languages), so that I end up with a waste of network bandwidth and filtering/handpicking the desired data in the business side.

The DB in question is PostgreSQL, but a RDMBS-agnostic way would be nice.

Answer 1

try something like this:

SELECT
    e.key,COALESCE(o.value,e.value)
    FROM Translation                e
        LEFT OUTER JOIN Translation o ON e.key=o.key and o.language_code='nl'
    WHERE e.language_code='en'