Make SELECT return data in the default language when it can not find a translation

Question

Make SELECT return data in the default language when it can not find a translation

Navigation

#1 by (5 votes)
#2 by (3 votes)
#3 by (2 votes)
#4 by (1 votes)

1

I'm building a multi-language foundation.

I have a select with the chosen language, but there is no guarantee that 100% of the content is translated, so I need to make a kind of or for 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 lowest ID, in the 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 language is not found.

mysql

asked by anonymous 08.01.2015 / 20:29

4 answers

Doubt about using the SPA Template of VS2015 I have sets of variables storing different settings. How to use a specific set depending on the occasion?

score 5 · Answer 1

If I understand correctly, you have no guarantee as to which ID will be returned. It is difficult to give a precise answer without knowing more about your bank, but I would make a JOIN for the content in each language (the chosen one and the 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

score 3 · Answer 2

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

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

In the values of the FIELD function, you first enter the language you are looking for and then the default language. More information on the FIELD function.

score 2 · Answer 3

The solution with function would be something of the type (without precise 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 " "

Do not select something like

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

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

score 1 · Answer 4

You can search for the desired language ( en-us ) or the default language case strong> the desired language is not present. For this last condition you can use the not exists clause on a subquery connected to the main query.

It's not clear to me which column you're going to use to get the translation, so I'm going to use a hypothetical table and you can use the technique by embedding the code for 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 left the query large but it is actually quite simple.