MySQL语言翻译查询

卡姆勒什·苏塔(Kamlesh Suthar)

**我在数据库中使用以下方法**我正在使用多语言支持的数据库。但是我不知道如何从基于语言的数据库中查询翻译的文本。

这是我的表的DDL。请问我有什么问题。

分类表

CREATE TABLE IF NOT EXISTS `catalog_category` (
  `id` int(10) unsigned NOT NULL,
  `name_translation_id` int(10) unsigned NOT NULL,
  `description_translation_id` int(10) unsigned NOT NULL,

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

语言表

CREATE TABLE IF NOT EXISTS `languages` (
  `id` int(10) unsigned NOT NULL,
  `language_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `language_code` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

语言翻译表

CREATE TABLE IF NOT EXISTS `language_translation` (
  `id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

语言翻译输入表

CREATE TABLE IF NOT EXISTS `language_translation_entry` (
  `id` int(10) unsigned NOT NULL,
  `translation_id` int(10) unsigned NOT NULL,
  `language_id` int(10) unsigned NOT NULL,
  `translation_text` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

目前,我正在使用此查询来获取结果,但它只是从名字上给了我。我也要在结果中说明。

SELECT lte.translation_text  as name FROM catalog_category AS cc
LEFT JOIN language_translation lt ON lt.id = cc.name_translation_id
LEFT JOIN language_translation_entry AS lte ON lte.translation_id = lt.id
LEFT JOIN languages AS l on l.id = lte.language_id
WHERE l.language_code = "en"

我需要从Translation Entry Table中获取翻译后的文本,以根据单个查询中的语言代码获得类别名称和描述。

耀斑

您可以使用子选择:

SELECT 
  (select lte.translation_text
   from language_translation_entry AS lte 
   JOIN languages AS l on l.id = lte.language_id
   WHERE lte.translation_id = cc.name_translation_id 
         and l.language_code = "en"
  ) as name,
  (select lte.translation_text
   from language_translation_entry AS lte 
   JOIN languages AS l on l.id = lte.language_id
   WHERE lte.translation_id = cc.description_translation_id 
         and l.language_code = "en"
  ) as description
from catalog_category AS cc

假定每个对象最多有1个条目(translation_id, laguage_id),否则将给您一个错误,因为这些子选择只能返回一个值。

我省略了该表language_translation,因为您可能仅使用它来获取自动增量值,并且联接不需要。

一种替代方法是对join需要翻译的每一列使用另一种,例如,可以使用

SELECT lte.translation_text as name,
       lt2.translation_text as description,
FROM catalog_category AS cc
LEFT JOIN language_translation_entry AS lte 
ON lte.translation_id = cc.name_translation_id
LEFT JOIN languages AS l on l.id = lte.language_id
LEFT JOIN language_translation_entry AS lte2 
ON lte2.translation_id = cc.name_translation_id 
   and lte2.language_id = l.id
WHERE l.language_code = "en"

我假设您希望两种翻译都使用相同的语言。

顺便说一句,left join实际上,它在这里像普通法一样工作join,因为使用where l.language_code = "en",它必须存在。如果您想要一个real left join,则必须将该条件移至on-clause,因此它可能会丢失(如果您的翻译有可能丢失,而您仍然想从查询中得到结果)。对于description,已经允许丢失它以使代码更短,否则您必须将条件从on移至where子句,因此将最后一部分更改为

LEFT JOIN language_translation_entry AS lte2 
ON lte2.translation_id = cc.name_translation_id 
WHERE l.language_code = "en" and lte2.language_id = l.language_id

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章