Kamlesh Suthar Kamlesh Suthar - 2 months ago 15
MySQL Question

MySQL Language translation query

**I Used below approach for database**
I am using multi language supported database. But i don't know how to query translated text from database based on Language.

Here is my DDL for tables. Please ask me if you have any question.

Category Table

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;


Language Table

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;


Language Translation table

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


Language Translation Entry Table

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;


Currently i am using this query to get results but it is giving me from only name. I want description also in results.

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"


I need to get translated text from Translation Entry Table for category name and description based on language code in a single query.

Answer

You can use subselects:

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

This assumes you have max 1 entry for each (translation_id, laguage_id), otherwise this will give you an error, because these subselects can only return one value.

I left out the table language_translation, because you probably only use it to get your autoincrement values, and is not required for the join.

An alternative would be to use another join for each column you need translated, you can e.g. use

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"

where I assume that you want both translation to be the same language.

Btw., left join actually works here like a normal join, since you use where l.language_code = "en", it has to exists. If you want a real left join, you have to move that condition to the on-clause, so it can be missing (if there is a possibility that your translations are missing and you still want to get a result from your query). For description, it already is allowed to be missing to make the code shorter, otherwise you would have to move the condition from on to the where clause, so change the last part to

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
Comments