[MySQL] meertalig nieuwssysteem met fallback

[Verwijderd]

Beetje onduidelijke titel misschien, maar het ``probleem'' is als volgt: een site heeft een nieuwssysteem waarbij nieuwsberichten altijd in het engels aanwezig zijn, en wellicht ook in een andere taal, dat dan de voorkeurstaal van de gebruiker kan zijn. Op het moment dat een gebruiker de nieuwspagina bekijkt is het de bedoeling dat alle nieuwsitems worden getoond. Deze het liefst in de voorkeurstaal, maar is die niet aanwezig, dan in het Engels.

Op zich niet echt heel moeilijk, maar het probleem was dat de rest van de site allemaal al klaar was en alles gebaseerd was op een functie die 1 query result als waarde teruggaf, en de rest niet echt meer aanpasbaar was. (ja, ik weet het maar ik had de site niet gemaakt). Gelukkig werd er gebruik gemaakt van mysql 5, en was het met een query als de volgende te doen (even uit mijn hoofd, de echte was nog erger):

SQL:

(SELECT * FROM news_text WHERE language=$prefered_language)
UNION
(SELECT * FROM news_text WHERE language="engels" AND news_id NOT IN (SELECT * FROM news_text WHERE language=$prefered_language))

Op zich werkt het wel, maar het is wel een idioot zware query voor zoiets simpels. Bovendien is het niet echt mogelijk zoiets te migreren naar een andere server, aangezien de meeste servers toch nog mysql 4.0 draaien. Heeft iemand enig idee hoe zoiets, gezien de gestelde voorwaarden, toch makkelijker kan?

[RobIII]

SQL:

SELECT * FROM news_text WHERE id=123 and (language="engels" or language=$prefered_language)

Ik neem aan dat je voor een nieuwsbericht een ID ofzo meegeeft in de where clause. Op deze manier krijg je 1 of beide talen bericht terug. Daarna is het een kwestie van afhandelen in je script.

[ Voor 52% gewijzigd door RobIII op 17-08-2006 23:07 ]

[Verwijderd]

RobIII schreef op donderdag 17 augustus 2006 @ 23:05:

SQL:

SELECT * FROM news_text WHERE id=123 and (language="engels" or language=$prefered_language)

Ik neem aan dat je voor een nieuwsbericht een ID ofzo meegeeft in de where clause. Op deze manier krijg je 1 of beide talen bericht terug. Daarna is het een kwestie van afhandelen in je script.

Het ging om een lijst met newsitems, de WHERE id=$X heeft niet veel nut. En het afhandelen in het script was ook geen optie, dat was juist het hele probleem.

[RobIII]

My bad, ik had het verkeerd begrepen. Binnen de gestelde voorwaarden is je query helemaal zo slecht nog niet (ik neem aan dat de lijst zowieso geen honderduizenden records bevat als je geen limit/top gebruikt). Met de beperkingen die je hebt is het sowieso gewoon roeien met de riemen die je hebt. Als je nou kon uitgaan van MySQL 5 dan kon je nog e.e.a. vangen in een SP ofzo...

[ Voor 29% gewijzigd door RobIII op 17-08-2006 23:16 ]

[P_de_B]

Heb je ook iets dat een nieuwsbericht uniek maakt? Dus 1 unieke ID voor hetzelfde nieuwsbericht in de verschillende talen. Zoals ik het nu zie is ID verschillend voor iedere taalversie van hetzelfde bericht.

[bigbeng]

@P_de_B: Volgens mij is het idee van zijn query dat hij alle berichten ophaalt in de voorkeurstaal met een union van alle berichten in het engels die niet in de voorkeurstaal bestaan. Dat suggereert toch dat news_id een nieuwsbericht uniek maakt over alle talen heen.

@TS: Je kunt de query misschien iets minder zwaar maken door het news_id uit de hoofdquery op te nemen in de subquery van het tweede deel van je union. Cryptisch? Dacht het wel Uitgewerkt dus:

SQL:

(SELECT * FROM news_text WHERE language=$prefered_language)
UNION
(SELECT * FROM news_text nt1
 WHERE language="engels"
 AND news_id NOT IN 
 (
    SELECT * FROM news_text nt2 WHERE language=$prefered_language
    AND nt2.news_id = nt1.news_id
 )
)

Er vanuit gaande dat mysql een dergelijke constructie ondersteunt natuurlijk.

edit:

overigens is dit een typisch gevalletje van een NOT EXISTS (in een andere gedaante), dus je zou kunnen kijken of je die kan herschrijven volgens de MySQL manual zodat ie ook voor 3.23 en 4.0 werkt

[ Voor 12% gewijzigd door bigbeng op 18-08-2006 13:25 ]

[Thekk]

SQL:

SELECT *
FROM news_text AS NT1
WHERE 
  (NOT EXISTS
    (SELECT * 
     FROM news_text as NT2 
     WHERE NT2.language = $prefered_language and NT2.news_id = NT1.news_id) 
   AND NT1.language = $default_language)
OR 
  NT1.language=$prefered_language;

Zoiets? Geen union, wel een subquery, maar daar ontkom je denk ik niet aan.

[ Voor 9% gewijzigd door Thekk op 18-08-2006 14:57 ]

[P_de_B]

bigbeng schreef op vrijdag 18 augustus 2006 @ 13:20:
@P_de_B: Volgens mij is het idee van zijn query dat hij alle berichten ophaalt in de voorkeurstaal met een union van alle berichten in het engels die niet in de voorkeurstaal bestaan. Dat suggereert toch dat news_id een nieuwsbericht uniek maakt over alle talen heen.

Ja, maar ik vroeg naar een unieke id voor ieder bericht(onderwerp), en niet voor iedere taalvariant..Zoals het nu is, jeeft volgens mij iedere taalvariant een nieuw id.

Stel dat we een bericht hebben: "Meer regen in augustus", en de Engelse variant "More rain in august". Waar ik naar op zoek ben is een id die voor beide berichten hetzelfde is.

NieuwsId, ID, Nieuws, Taal
1,1,"Meer regen in augustus", NL
1,2,"More rain in august", EN

[Crazy D]

Iets als

SQL:

select pl.NieuwsID, IsNull(pl.Tekst, dl.Tekst)
from Nieuwsberichten dl
    left join Nieuwsberichten pl on pl.NieuwsID = dl.NieuwsiD and pl.Language = $preflanguage
where dl.Language = 'EN'

Gewoon left joinen met zichzelf op de voorkeurstaal, indien het bericht niet bestaat in die taal, de default taal gebruiken. (alias dl = default language, pl = preferred language)

Maar dan ga ik er wel vanuit dat een nieuwsbericht altijd 1 ID heeft (het NieuwsID), want anders is het volgens mij onmogelijk...

[Thekk]

Crazy D schreef op vrijdag 18 augustus 2006 @ 15:05:
Iets als

SQL:

select pl.NieuwsID, COALESCE(pl.Tekst, dl.Tekst)
from Nieuwsberichten dl
    left join Nieuwsberichten pl on pl.NieuwsID = dl.NieuwsiD and pl.Language = $preflanguage
where dl.Language = 'EN'

Buitengewoon elegante oplossing, alleen moet de isnull veranderd worden in coalesce().

[ Voor 7% gewijzigd door Thekk op 18-08-2006 15:17 ]

[bigbeng]

P_de_B schreef op vrijdag 18 augustus 2006 @ 14:49:
[...]

Ja, maar ik vroeg naar een unieke id voor ieder bericht(onderwerp), en niet voor iedere taalvariant..Zoals het nu is, jeeft volgens mij iedere taalvariant een nieuw id.

Stel dat we een bericht hebben: "Meer regen in augustus", en de Engelse variant "More rain in august". Waar ik naar op zoek ben is een id die voor beide berichten hetzelfde is.

NieuwsId, ID, Nieuws, Taal
1,1,"Meer regen in augustus", NL
1,2,"More rain in august", EN

Volgens mij lullen we langs elkaar. De manier waarop zijn union in elkaar zit suggereert bij mij dat news_id de rol van NieuwsId vervult zoals jij die bedoelt. Anders zou query in de TS toch alle records voor alle talen ophalen?

offtopic:
Woot! 1000 berichten . Niet dat ik erop aan het letten was natuurlijk . Krijg ik nu ook rechten op de HK? Das toch waar de opa's rondhangen

[ Voor 8% gewijzigd door bigbeng op 18-08-2006 15:42 ]

[Verwijderd]

P_de_B schreef op vrijdag 18 augustus 2006 @ 14:49:
[...]

Ja, maar ik vroeg naar een unieke id voor ieder bericht(onderwerp), en niet voor iedere taalvariant..Zoals het nu is, jeeft volgens mij iedere taalvariant een nieuw id.

Stel dat we een bericht hebben: "Meer regen in augustus", en de Engelse variant "More rain in august". Waar ik naar op zoek ben is een id die voor beide berichten hetzelfde is.

NieuwsId, ID, Nieuws, Taal
1,1,"Meer regen in augustus", NL
1,2,"More rain in august", EN

En dat is dus niet zo, de primary key wordt gevormd door de combinatie van news_id en language