Make SELECT return data in default language when no translation can be found

Question

I’m building a multi-language base.

I have a select with the chosen language, but there is no guarantee that 100% of the content is translated, so I need to do a sort of or to the default language as a secondary alternative.

select ...
 where ...
   and (idioma = 'pt-br' or idioma = 'en-us')

There is a relationship in this query, but that’s beside the point. The problem is that when I have entries in both languages, select always returns the with smaller ID, in case en-us:

id | idioma | texto
1  | en-us  | ...
2  | pt-br  | ...
3  | it     | ...

What I need is to prioritize the desired language, and bring the default only when the desired one is not found.

Answer 1

If I understand correctly, you have no guarantee of which ID will be returned. It’s hard to give an accurate answer without knowing more about your bank, but I would make a JOIN for content in each language (chosen and default), and a query in this line:

SELECT
   -- ...
   COALESCE(conteudo_en.texto, conteudo_pt.texto) AS texto
FROM paginas pagina
   INNER JOIN conteudos conteudo_pt
   ON conteudo_pt.pagina_id = pagina.id
   AND conteudo_pt.idioma = 'pt-br'
   LEFT OUTER JOIN conteudos conteudo_en
   ON conteudo_en.pagina_id = pagina.id
   AND conteudo_en.idioma = 'en-us'
WHERE pagina.id = 1

Answer 2

You can use a ORDER BY and LIMIT 1, as in the example below:

ORDER BY FIELD(idioma, 'en-us', 'pt-br'), idioma LIMIT 1

In the function values FIELD you put first the language you are searching for and then the default language. More information about the function FIELD.

Answer 3

The solution with Function would be something of the type (without accurate syntax)

FUNCTION OBTER_PAGINA (PID,PIDIOMA)

 SELECT TEXTO
 FROM   TABELA
 WHERE ID = PID
 AND   IDIOMA = PIDIOMA;

 SE LOCALIZOU RETORNA TEXTO
 SE NAO LOCALIZOU
  SELECT TEXTO
  FROM   TABELA
  WHERE ID = PID
  AND   IDIOMA = 'EN-US;
  SE LOCALIZOU RETORNA TEXTO
  SE NAO LOCALIZOU RETORNA " "

No select something like

SELECT OBTER_PAGINA(1234,'PT-BR') FROM ...

It is not a code EXACTLY but basic idea to work the question, the advantage is that the implementation of the text search is encapsulated in FUNCTION.

Answer 4

You can search for the desired language (en-us) or the default language (en) case the desired language is not present. For this last condition you can use the clasp not exists about a subquery connected with the main query.

It is not clear to me which column you will rely on to fetch the translation, so I will use a hypothetical table and you can use the technique by adapting the code to your actual table.

Table:

id | idioma | texto_original | texto_traduzido
1  | en-us  | ...            | ...
2  | pt-br  | ...            | ...
3  | it     | ...            | ...

SQL command:

select 
    texto_traduzido
from 
    traduções
where
    (
        texto_original = 'casa'
        and idioma = 'en-us'
    )
    or 
    (
        texto_original = 'casa'
        and idioma = 'pt-br'
        and not exists
        (
            select 
                texto_original
            from 
                traduções as idioma_desejado
            where
                idioma_desejado.texto_original = traduções.texto_original
                and idioma_desejado.idioma = 'en-us'
        )
    ) 

_{My indentation made the query great but it’s actually quite simple.}