开发者

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'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜