0

Related to this question, I want to return module contents for various regions/languages. I built my site in English so I know I have all of those pieces, but since I'm getting translations from an outside agency I might be missing pieces. We want those pieces to fallback to English when in doubt.

Here are my tables:

languages

id | tag   | region        | language
--   -----   -------------   --------
1    en-US   United States   English
2    es-ES   Spain           Spanish
...
modules

id | module
--   ------
1    header
2    main
3    footer
contents

id | language_id | module_id | content
--   -----------   ---------   -------
1    1             1           This is my header
2    1             2           This is my main
2    1             3           This is my footer
3    2             1           Este es mi encabezado
4    2             3           Este es mi pie de página

See here, I am missing the 'main' module (module_id=2) for 'es-ES' (language_id=2)

Here's how I'm pulling my data:

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id
LEFT JOIN languages l ON c.language_id = l.id
WHERE l.tag = 'es-ES' OR l.tag IS NULL

which returns

module | content
------   -------
header   Este es mi encabezado
main     null
footer   Este es mi pie de página

I want that null value for 'main' to fallback to the English version ("This is my main") Is there a simple way to do this that I'm missing?

1 Answer 1

2

You need something similar to

SELECT m.module, 
       COALESCE(c_esp.content, c_eng.content) content
FROM `modules` m
LEFT JOIN ( contents c_esp, 
            languages l_esp ) ON (     m.id = c_esp.module_id 
                                   AND c_esp.language_id = l_esp.id 
                                   AND l_esp.tag = 'es-ES' )
LEFT JOIN ( contents c_eng, 
            languages l_eng ) ON (     m.id = c_eng.module_id 
                                   AND c_eng.language_id = l_eng.id 
                                   AND l_eng.tag = 'es-ES' )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.