我正在使用多语言支持的数据库。但是我不知道如何从基于语言的数据库中查询翻译的文本。
这是我的表的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] 删除。
我来说两句