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

问题描述

我必须从数据库中获取 i18n 文本.默认语言是英语，它有 everything 的文本.但非英语语言不一定有所有所需的翻译.如果数据库中没有某个实体/键的非英语翻译，那么我希望它返回英文文本.所以，英语是这里的后备语言.

i18n 文本表是这样的(PostgreSQL 方言):

CREATE TABLE 翻译 (id 序列主键，language_code CHAR(2) NOT NULL,键 VARCHAR(20) 非空，值文本不为空，CONSTRAINT translation_unique UNIQUE (language_code, key))

数据如下:

INSERT INTO 翻译(语言代码、键、值)价值观('en', 'foo', 'foo in English'),('nl', 'foo', 'foo in Nederlands (Dutch)'),('en', 'bar', 'bar in English')

我想基本上做下面的伪 SQL 查询:

SELECT 键、值从翻译WHERE (language_code = 'nl' OR IF value IS NULL THEN language_code = 'en')

(其实'nl'的值是要参数化的)

所以它返回以下内容:

<上一页>+-----+--------------+|关键 |价值 |+-----+--------------+|富 |foo 在Nederlands (荷兰语) | 世界银行|酒吧 |酒吧英文|+-----+--------------+

如何在单个 SQL 查询中实现这一点?

有问题的数据库是 PostgreSQL，但与 RDMBS 无关的方式会很好.

解决方案

试试这样的:

选择e.key,COALESCE(o.value,e.value)FROM 翻译 eLEFT OUTER JOIN 翻译 o ON e.key=o.key and o.language_code='nl'WHERE e.language_code='en'

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 the below pseudo SQL query:

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 achieve this in a single SQL query?

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

解决方案

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'

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