CODEWITHSUNDEEP
mysqlsqldatabaseleft-jointranslation
Jos
Jos

Reputation: 1484

SQL to replace field content with content from another table if found

Consider the following tables for a multilingual event database

events
  id, title, content, ...

localizations
  id, table_name, row_id, field, language, text

What my CMS does is in case of translation store that translations in the localizations table. When retrieving events, the fields get overwritten by their localized texts for the appropriate language.

Thus e.g.:

events
  1, 'Great event', 'lorum ipsum', ...
  2, 'Another event', 'lorum ipsum', ...

localizations
  1, 'events', 1, 'title', 'es', 'Gran evento'

Question: is it possible to construct a mysql query that retrieves all events, with their localized titles for english, or default if not found?

I suspect it has to be a left join like this

SELECT e.id, e.title, e.content 
FROM `events` e 
LEFT JOIN localizations loc ON loc.row_id = e.id

But it has to join only if loc.field = 'title' and loc.language='en'

Upvotes: 1

Views: 463

Answers (1)

O. Jones
O. Jones

Reputation: 108736

The trick here is LEFT JOIN as you have already discovered, combined with COALESCE(). That function returns the first non-null value from among its arguments.

 SELECT e.id, e.title,
        COALESCE(ca.text, es.text, e.content) content
   FROM events e
   LEFT JOIN localizations ca ON ca.row_id = e.id AND ca.language = 'ca'
   LEFT JOIN localizations es ON es.row_id = e.id AND es.language = 'es'

From this, in the result set's content column, you will get the Catalan text if it is there. If not, and the Spanish text is there, you'll get that. Otherwise you'll get whatever is in the main table.

Upvotes: 1

Related Questions