使用后备语言检索i18n数据 [英] Retrieving i18n data with fallback language

问题描述

我必须从数据库中获取i18n文本。默认语言是英语，其中包含 everything 的文本。但是非英语语言不一定具有所有所需的翻译。如果数据库中没有针对某个实体/键的非英文翻译，那么我想让它返回英文文本。因此，这里的英语是 fallback 语言。

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.

i18n文本表如下所示（PostgreSQL方言）：

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)
)

数据如下所示：

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基本上想执行以下伪SQL查询：

I'd like to basically do the below pseudo SQL query:

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

（实际上是'nl'值

，使其返回以下内容：

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

如何在单个SQL查询中实现？

How can I achieve this in a single SQL query?

所讨论的数据库是PostgreSQL，但与RDMBS无关会很好。

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

推荐答案

尝试如下操作：

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'

这篇关于使用后备语言检索i18n数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！