|
|
Posted by Jim Michaels on 01/09/06 10:01
what are the inputs and outputs?
my guess is the input is the item_id and lang_id. the output is item_text.
yes?
Jim
"Juliette" <jrf_no_spam@jokeaday.net> wrote in message
news:43847a0e$0$89138$dbd45001@news.wanadoo.nl...
> 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]
|