mysql translation tables with missing language fallback

Question

I have a database structure like this:

Countries

CREATE TABLE IF NOT EXISTS `countries` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code` varchar(2) COLLATE utf8_unicode_ci NOT NULL, `is_active` tinyint(1) NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `UNIQ_5D66EBAD77153098` (`code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Countries Language

CREATE TABLE IF NOT EXISTS `country_languages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `language_id` int(11) DEFAULT NULL, `country_id` int(11) DEFAULT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `IDX_1532561982F1BAF4` (`language_id`), KEY `IDX_15325619F92F3E70` (`country_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Language

CREATE TABLE IF NOT EXISTS `languages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `iso` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `active` tinyint(1) NOT NULL DEFAULT '1', `is_primary` tinyint(1) DEFAULT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `UNIQ_A0D153795E237E06` (`name`), UNIQUE KEY `UNIQ_A0D1537961587F41` (`iso`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And the data for languages table:

id name iso ---------------------- 1 English en 2 German de 3 Italian it

And country table:

id code ---------------- 1 ie

And country languages table

id country_id language_id name ---------------------------------------------- 1 1 1 Ireland in English 2 1 2 Ireland in German

I am trying to write a query that will return the following result in order to be able to display it. (if possible)

(Language) (Country name) English Ireland in English German Ireland in German Italian #NULL OR EMPTY STRING

And second I am trying to understand if it is possible to set for example english as the default language and when content for language with id 3 (Italian) is not present the default should fallback in as a result like this:

(Language) (Country name) English Ireland in English German Ireland in German Italian Ireland in English #please note language id is 3 -> Italian.

I'm confused by your example. the "country languages table", is that actually a table or is that the result of a query? Also the queries right after, it's just not clear what "id" is supposed to mean and how those data come together... — Jakumi
– Jakumi, Commented Aug 30, 2016 at 13:50
@Jakumi I have updated my question. I hope now it's more clear — Herr Nentu'
– Herr Nentu', Commented Aug 30, 2016 at 14:01

Jakumi · Accepted Answer · 2016-09-08 19:31:58Z

okay, so the following query can probably be done without a subquery but with a join instead. I'd trust the query optimizer does this, but I wouldn't be too sure.

SELECT l.name as language, (SELECT cl.name FROM country_languages cl WHERE cl.country_id=[the wanted country id] ORDER BY cl.language_id=l.id DESC, cl.language_id=1 DESC LIMIT 1) as country_name FROM languages l

In this version language_id 1 is used as the prefered fallback, you could probably add more languages in a similar manner. Using FIND_IN_SET instead as a second order criterion would work as well (FIND_IN_SET(cl.language_id,'1,2,3') DESC or whatever order you'd prefer).

Of course this query right now is for a fixed country_id. It could be extended in a similar manner for multiple countries with another join:

SELECT l.name as language, (SELECT cl.name FROM country_languages cl WHERE cl.country_id=c.id ORDER BY cl.language_id=l.id DESC, cl.language_id=1 DESC LIMIT 1) as country_name FROM countries c JOIN languages l

an alternative to subqueries would be to join the country_languages twice, and just select the first one not being null (which is probably one of the cleaner solutions):

SELECT l.name as language, COALESCE(first.name, second.name) as country_name FROM countries c JOIN languages l LEFT JOIN country_languages first ON (first.country_id=c.id AND first.language_id=l.id) LEFT JOIN country_languages second ON (second.country_id=c.id AND second.language_id=1)

If language id 1 is your fallback language. This can be expanded as well to provide multiple fallback languages ...

Thanks for the comment. Did not notice that the default value should be not for all countries...
One comment. Because I use Symfony and Doctrine instead of IFNULL COALESCE should be used. For the IFNULL people should write a DoctrineExtension class.

Gandharv · Accepted Answer · 2016-08-30 13:35:03Z

Here is the query for first part using joins:

SELECT `country_languages`.id, `country_languages`.country_id,`country_languages`.language_id, `country_languages`.name FROM `countries` left join country_languages on `countries`.id=`country_languages`.`country_id`

You can declare a status column in countries languages table and set status as 0,1,2 to mark priority as to how they will be fetched.

Collectives™ on Stack Overflow

mysql translation tables with missing language fallback

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related