使用后备语言检索 i18n 数据

发布于 2024-09-10 00:54:16 字数 1161 浏览 8 评论 0原文

我必须从数据库中获取 i18n 文本。默认语言是英语，它包含所有内容的文本。但非英语语言不一定具有所有所需的翻译。如果某个实体/键的非英语翻译在数据库中不可用，那么我希望它返回英语文本。因此，英语是这里的后备语言。

i18n 文本表看起来像这样（PostgreSQL 方言）：

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

我基本上想执行以下伪 SQL 查询：

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 查询中实现此目的

所涉及的数据库是 PostgreSQL，但与 RDMBS 无关的方式会更好。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

清晨说晚安 2024-09-17 00:54:16

尝试这样的事情：

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'

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'

回复收藏 0 原文

~没有更多了~