|
|
Posted by Juliette on 11/23/05 16:18
Hi all,
I'm trying to reduce some query overhead by reducing the recordset
returned from a query and I can't seem to get my head round how to do it
using subqueries.
Situation:
Multiligual website with versionmanagement of the maintext and the
translations.
Each language has to be approved before it should be shown.
Each text item has to be approved before it should be shown.
Each translated item has to be approved before it should be shown.
The translation may not be more than one version behind the default text
version.
If no (approved) translation to the requested language is found, the
default language has to be displayed, assuming that there is always an
approved version of the default language available.
There are more criteria, but those aren't giving me problems, so this is
what a simplified version of the (normalized relational) table layout
looks like:
Table text_items
item_id | approved
001 | 2005-10-10
002 | 2005-11-01
003 | 2005-11-22
Table text_content
row_id | lang_id | item_id | item_version | translation_version |
item_text | approved
001 | 001 | 001 | 1 | 1 | original text1| 2005-10-10
002 | 001 | 002 | 1 | 1 | original text2| 2005-11-01
003 | 001 | 002 | 2 | 1 | changed text2 | 2005-11-05
*004 | 001 | 002 | 3 | 1 | changed text2 | 2005-11-15
*005 | 001 | 003 | 1 | 1 | original text3 | 2005-11-20
006 | 002 | 001 | 1 | 1 | original transl1 | 2005-10-11
*007 | 002 | 001 | 1 | 2 | changed transl1 | 2005-10-30
008 | 002 | 002 | 1 | 1 | original trans2 | 2005-11-02
Table available_languages
lang_id | approved
001 | 2005-10-10 (= default language)
002 | 2005-10-12
Required results:
The three 3 rows with astrixes in front of them should be returned:
for item 001: content item 007 : the translation which has been approved
and is of the same item version as the default
for item 002: content item 004 : the changed default language text as
the translation is of version 1 of the item and the default language is
on version 3
for item 003: content item 005 : no translation can be found, so return
the default language
The logic I am looking for is something along the lines of:
SELECT tc.item_text
FROM text_content as tc, text_items as item, available_languages as lang
WHERE item.item_id = tc.item_id
AND tc.lang_id = lang.lang_id
AND item.approved <= CURDATE()
AND IF FOUND (
tc.lang_id = 002
AND tc.approved <= CURDATE()
AND lang.approved <= CURDATE()
AND item.approved <= CURDATE()
AND MAX(tc.translation_version)
AND ( ( MAX(tc.item_version WHERE tc.lang_id = 001) -
MAX(tc.item_version WHERE tc.lang_id = 002) ) < 2 )
)
ELSE (
tc.lang_id = 001
AND tc.approved <= CURDATE()
AND lang.approved <= CURDATE()
AND item.approved <= CURDATE()
AND MAX(tc.translation_version)
AND MAX(tc.item_version)
)
Until now I had a "left join to self" in place which returned a two
column table with default language as column 1 and if available & all
criteria fullfilled the translation in column 2.
With php logic I would then test if column 2 was empty or not, if not,
display the content of column2, else display the content of column 1.
Any help would be much appreciated !
Thanks in advance,
Juliette
Navigation:
[Reply to this message]
|