CODEWITHSUNDEEP
mysqldatabasedatabase-designtranslationmultilingual
Sirac
Sirac

Reputation: 693

Multilingual database - Get all languages of post

I want to write an application where multilingual data is stored in a database. There are posts with an unique ID which are written in several languages (at least 2 languages, other languages will possibly be added). I already looked through several posts of stackoverflow and found these nearly satisfying answers:
Multi-language Category Titles in Mysql Database
What's the best database structure to keep multilingual data?
Schema for a multilanguage database

They all suggest to put up 3 tables like this:

[ languages ]
id (INT)
name (VARCHAR)

[ products ]
id (INT)
price (DECIMAL)

[ translation ]
id (INT, PK)
model (VARCHAR) // product
field (VARCHAR) // name
language_id (INT, FK) 
text (VARCHAR)

(This is from one of the links above)

I agree with that solution, but I want the application to show all the possible languages the post is written in. I already thought of reading all the translations for a post and using only one to create the page, but use the other translations to show other possible languages. I don't like this idea, because that way I retrieve too much data from the database that isn't used (because I retrieve many translations an use only one).
I also thought of retrieving all possible translations and storing them already in the page (e.g. in javascript code, in a data-attribute, etc.), but this would make the page slower, because I think most users only want to see the post in only one language.
So I guess the application has to execute 2 queries: one to get the translation and one to get all languages for the post. But I think there is a way to put this in one query, but I don't have a good idea how to do that.

Is there a easy way to read the translation for a language (given by the user or default, maybe just the first database entry) and all other language the post is written in with just a single query?

Thanks in advance.

Upvotes: 0

Views: 775

Answers (1)

Rimas
Rimas

Reputation: 6024

I recommend you to use two queries for higher flexibility.

Also you can use one query - for example to get languages separated by "," in a string:

SELECT t.*,
  (SELECT GROUP_CONCAT(l1.name SEPARATOR ',')
    FROM translation t1
    JOIN languages l1 ON l1.id = t1.language_id
    WHERE t1.product_id = t.product_id
    GROUP BY t1.product_id
  ) AS lang_names
FROM translation t
  -- there put required JOIN-s --
  WHERE t.id = ?

there: ? is placeholder for translation id which you wish to show. I assume there is product_id field in the translation table.

Upvotes: 2

Related Questions