多语言 MySQL 内容：如果指定的语言不可用，如何选择给定的语言或另一种语言？

Question

我正在开发一个多语言网站 PHP，并且希望获取给定语言的内容（如果可用）和另一种语言的内容（如果不可用）。我将在下面尝试解释我的问题。如果有不清楚的地方，请告诉我。

我的表格：

• 内容：content_id、url、日期
• content_l10n：content_id、l10n_id、标题、说明
• l10n：l10n_id、 name, order

第一种情况：

• 我的访客说法语。
• 我想要显示的内容有英语和法语两种版本。
• 该网站应显示法语内容。

→ 易于使用 JOIN 实现。

第二种情况：

• 我的访客说法语。
• 我想要显示的内容仅提供英文。
• 网站应显示英文内容。

→ 如何实现？是否可以在单个请求中实现？

一些注意事项：

• 这必须可以扩展到两种以上的语言。
• 该解决方案必须适用于这两种情况，因为我不知道在执行请求之前内容是否以正确的语言提供。
• 速度越快越好。
• 如果需要，可以更改表格。
• 我想选择多个 content 行（例如，对于标题列表）。
• 有时，title 已翻译，但 description 为 NULL。理想情况下，请求会选择给定语言的标题，但会回退到另一种语言进行描述。
• 最好设置后备的顺序（首先：给定语言，然后：按 order ASC 排序的 l10n 中的语言）。

我们将非常感谢您的帮助！先感谢您 ！

亲切的问候，

奥利维尔

Answer 1

此解决方案适用于许多行，但每种语言需要 1 个 LEFT JOIN，并且 JOIn 的顺序指定优先级。

SELECT   c.url, c.date, 
         COALESCE( c1.title, c2.title ),
         COALESCE( c1.description, c2.description )
FROM      content c
LEFT JOIN content_l10n c1 ON (c1.content_id = c.content_id AND c1.l10n_id=$1)
LEFT JOIN content_l10n c2 ON (c2.content_id = c.content_id AND c2.l10n_id=$2)

（注意：我假设 $1 和 $2 是用户的前 2 种首选语言，并且它们缓存在会话中，因此不需要使用 l10n 进行额外的 JOIN）。

对于您的表结构，这是设置语言顺序的唯一有意义的方法。您需要一个额外的表来指定每个用户的语言首选项，而不是将订单存储在 l10n 表中。因此，假设您有一个表

user_l10n( user_id, l10n_id, order )

，并且假设表 l10n 保留其“order”字段作为默认值。

如果这样做：

SELECT   ..., COALESCE(ul.order,l.order) AS order
FROM      
          content      c
JOIN      content_l10n cl USING (content_id)
JOIN      l10n         l  USING (l10n_id)                -- get default language order
LEFT JOIN user_l10n    ul ON    (ul.l10n_id=l.l10n_id    -- get user preferences if available
                                 AND ul.user_id=$user_id)
WHERE search condition on content, etc
ORDER BY content_id, COALESCE(ul.order,l.order)

您将获得所有匹配的文档，以及用户指定的（或默认的）排序，因此应用程序可以轻松地解决这个问题。

现在的想法是避免从数据库中检索被用户喜欢的语言的现有翻译“遮蔽”的语言的所有行。

执行此操作的自然方法是 GROUP BY，但是 MySQL 没有可以在这里工作的聚合函数...

您可以执行依赖子查询（用于标题和描述）；抓取一行还可以，但如果你想抓取多行，速度就非常慢。

但你也可以做点别的事！这依赖于 MySQL 的非标准 GROUP BY 子句的一些可疑行为...

首先，收集要显示的“content_id”列表（分页搜索查询的结果，等等）。然后你就可以做类似下面恐怖的事情：

SELECT * FROM
(
    SELECT content_id, title FROM 
    (
        SELECT    c.content_id, c.title
        FROM      
                  content      c
        JOIN      content_l10n cl USING (content_id)
        JOIN      l10n         l  USING (l10n_id)
        LEFT JOIN user_l10n    ul ON    (ul.l10n_id=l.l10n_id AND ul.user_id=$user_id)
        WHERE cl.content_id IN ($list) AND c.title IS NOT NULL
        ORDER BY content_id, COALESCE(ul.order,l.order)
    ) d GROUP BY content_id
) t
JOIN
(
    SELECT content_id, description FROM 
    (
        SELECT    c.content_id, c.description
        FROM      
                  content      c
        JOIN      content_l10n cl USING (content_id)
        JOIN      l10n         l  USING (l10n_id)
        LEFT JOIN user_l10n    ul ON    (ul.l10n_id=l.l10n_id AND ul.user_id=$user_id)
        WHERE cl.content_id IN ($list) AND c.description IS NOT NULL
        ORDER BY content_id, COALESCE(ul.order,l.order)
    ) d GROUP BY content_id
)
USING (content_id)

Answer 2

这应该为您提供第二种情况的基础：

SELECT   content.url, content.date, content_l10n.title, content_l10n.description
FROM     content, content_l10n, l10n
WHERE    content.content_id = content_l10n.content_id AND
         content_l10n.l10n_id = l10n.l10n_id AND
         content.content_id = {$contentId}
ORDER BY l10n.order ASC
LIMIT    1

关于性能，您需要在 content.content_id、content_l10n.content_id、l10n.l10n_id 中建立索引> 和l10n.order。