Selecting multiple translated articles (records)
How do I select i.e. 10 articles without having to run 1 query to fetch each article translation (a total of 11 queries)? I have 3 tables; cultures, articles, and translations:
Cultures
id code language
-- ---- ---------
1 en English
2 no Norwegian
Articles
id title content
-- ----- -------------
1 Home Hello, world!
2 About This is me...
...
Translations
id culture object FK field value
-- ------- ------ -- ------- ---------------
1 1 Page 1 title Home
2 1 Page 1 content Hello, world!
3 2 Page 1 title Hjem
4 2 Page 1 content Hei, verden!
5 1 Page 2 title About
6 1 Page 2 content This is me...
7 2 Page 2 title Om
8 2 Page 2 content Dette er meg...
...
Any help would be greatly appreciated!
Edit: Say I want to fetch all the articles, translated to norwegian. This is my incomplete query (it does not YET connect FK to a.id):
SELECT
a.id,
(SELECT tr.value FROM translations tr WHERE tr.object='Page'
AND field='title' AND tr.culture=2) as title,
(SELECT tr.value FROM translations tr WHERE tr.object='Page'
AND tr.field='content' AND tr.culture=2) as content
FROM
articles a
Edit 2: Same issue as this: Best mysql query for开发者_开发问答 selecting multiple rows that EACH need multiple associative rows from another table
Well, translations.FK
appears to be linked to the articles ID. So you select all articles, and for each article you select all translations.
With this query, your application code will then have to interpret the "title" / "content" stuff.
SELECT c.language, a.title, t.field, t.value
FROM articles a
JOIN translations t ON a.ID = t.FK
JOIN cultures c on t.culture = c.id
With this query, you get a 'title' and a 'content' column
SELECT a.title, t1.value as title, t2.value as content
FROM articles a
JOIN translations t1 ON a.ID = t1.FK and t1.field = 'title'
JOIN translations t2 ON a.ID = t2.FK and t2.field = 'content'
精彩评论