SQL连接，默认回退 [英] SQL join with default fallback

问题描述

我有一个名为电影的表，其中包含两列 description 和 longer_description 。这些列包含用于获取翻译的翻译键。我设法通过使用联接获取翻译，但是如果没有找到翻译，我想回退到默认语言环境。

I have a table called movies containing two columns, description and longer_description. These columns contain translation keys that are to be used for fetching translations. I managed to fetch the translations by using joins, but I want to fallback to a default locale if no translations are found.

我有这样的数据库结构：

I have a database structure like this:

电影桌

movies
---------------------------------------
name | description | longer_description

本地化表

localizations 
------------------------------
textkey | locale | translation

以textkey +语言环境作为主键。

with textkey + locale working as a primary key.

电影的示例数据：

name         | description        | longer_description
-------------------------------------------------------------
Batman movie | batman.description | batman.longer_description

这些值用于匹配本地化表中的textkey：

These values are used to match the textkey in localizations table:

本地化示例数据：

textkey                   | locale | translation
---------------------------------------------------------------
batman.description        | en     | english description
batman.longer_description | en     | english longer description

我要实现的是用给定的textkey + locale进行查询，如果存在翻译：选择（如果不翻译）：退回到默认 en 语言环境。

What I want to achieve is making a query with given textkey + locale, and if a translation exists: select, and if not: fallback to default en locale.

SELECT
    m.name,
    t1.translation AS description,
    t2.translation AS long_description
FROM
    movies m
LEFT OUTER JOIN
    localizations t1
ON
    t1.language = 'sv' AND
    t1.text_key = m.description
LEFT OUTER JOIN
    localizations t2
ON
    t2.language = 'sv' AND
    t2.text_key = m.long_description
ORDER BY
    m.name

这将为我提供所需的翻译，但无法解决不存在翻译的情况，我需要回退 en 语言环境。

This will get me the translations I want, but it will not solve the case where a translation doesn't exist and I need to fallback on the en locale.

推荐答案

为此使用存储功能：

create function fn_get_translation(p_key text, p_language text) returns text static strict language sql as $$
  select
    translation
  from
    localizations l join 
      (values(1,p_language),(2,'en')) as lng(ord,code) on (l.locale = lng.code)
  where
    l.text_key = p_key
  order by
    lng.ord
  limit 1
$$;

免责声明：功能未经测试，但我相信您将能够解决可能的错误。

Disclaimer: function not tested but i believe that you will be able to fix the possible errors.

然后查询将更简单：

select
  m.name,
  fn_get_translation(m.description, 'sv') as description,
  fn_get_translation(m.long_description, 'sv') as long_description
from
  movies m;

这篇关于SQL连接，默认回退的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！