[SQL] Met één query meerdere vertalingen ophalen

[HyperioN]

Ik ben bezig met een meertalige productendatabase.
Bepaalde waarden van een product staan vast, bijv. prijs en id. Maar een titel en omschrijving zijn meertalig.

Nu had ik bedacht dat de titel en omschrijving een id krijgen die verwijst naar een translations-tabel; waarin de content staat met meerdere talen.

Even een versimpelde weergave
table producten

id 	prijs 	titel_id 	omschr_id
1 	12.00 	1 		2
2 	86.00 	3 		4

table translations

id 	content_id 	lang 	content
1 	1 		nl 	Nederlandse titel Product 1
2 	2 		nl 	Nederlandse omschrijving product 1
3 	1 		en 	English title product 1
4 	2 		en 	English description product 1
5 	3 		nl 	Nederlandse titel Product 2
6 	4 		nl 	Nederlandse omschrijving product 2
7 	3 		en 	English title product 2
8 	4 		en 	English description product 2

Maar nu de query. Ik wil bijvoorbeeld de producten ophalen, in het Engels.
Nu had ik het volgende verzonnen:

SQL:

SELECT a.id AS productid, a.prijs, b.lang,  b.titel,c.omschrijving
FROM bart__producten a
JOIN (
 SELECT trans.content_id AS contentid, trans.content AS titel, trans.lang AS lang
 FROM bart__translations trans
) b ON (a.titel_id = b.contentid AND b.lang = 'en')
JOIN (
 SELECT trans.content_id AS contentid, trans.content AS omschrijving, trans.lang AS lang
 FROM bart__translations trans
) c ON (a.omschr_id = c.contentid AND c.lang = 'en')

Dat werkt prima:

productid 	prijs 	lang 	titel 				omschrijving
1 		12.00 	en 	English title product 1		English description product 1
2 		86.00 	en 	English title product 2		English description product 2

Ik vind de query alleen niet om aan te zien. Meerdere JOINs met elk een subquery .
Bovendien is deze namelijk niet echt lekker dynamisch. De query moet uiteindelijk met PHP dynamisch opgezet worden.
Dan kom ik de volgende "problemen" tegen:

• Ik moet per veld een JOIN doen. Als ik nog een veld wil toevoegen (bijv. specificaties) moet ik nog een JOIN toevoegen; deze bijvoorbeeld "d" noemen en deze ook in regel 1 van de query selecten (d.specificaties).
• Als ik de taal wil wijzigen; moet ik dat dus ook in iedere JOIN doen.
• Volgens mij creëert MySQL "onder de motorkap" per subquery een temp-table. Qua performance lijkt me deze query dus absoluut niet ideaal; met name als het aantal velden nog uitgebreid word en de query straks 150 producten ophaalt.

Nu heb ik uiteraard gegoogled en kom van alles tegen; maar echt wijzer wordt ik er niet van. De meeste voorbeelden zijn óf te simpel (spreken van één veld dat meerdere talen heeft), of juist zo uitgebreid dat ik er niks meer van snap (ook door het Engels).

Kan iemand mij een duwtje in de juiste richting geven voor een betere query?
Of is deze tabelstructuur in dit geval sowieso niet verstandig?

[Erwinvz1]

http://www.w3schools.com/sql/sql_union.asp
dit misschien?

[Arjan A]

Je datamodel is wel goed, al zou ik de taal ook een getal maken ipv 'en' of 'nl'.
Verder hoef je in een join niet een hele subquery te geven, maar volstaat de tabelnaam.
Dus:

SQL:

JOIN translations b ON a.titel_id = b.contentid AND b.lang = 'en'

Als je performance issues krijgt, kijk dan ook eens naar indexeringen. Velden waarop je JOINt moet je ook indexeren.

[ Voor 0% gewijzigd door Arjan A op 25-01-2012 11:17 . Reden: tabelnaam ]

[GlowMouse]

Je topictitel snap ik niet. Je haalt elke keer maar één vertaling op.

Arjan A schreef op woensdag 25 januari 2012 @ 11:11:
Als je performance issues krijgt, kijk dan ook eens naar indexeringen. Velden waarop je JOINt moet je ook indexeren.

In de tabel b dan welteverstaan.

[Arjan A]

GlowMouse schreef op woensdag 25 januari 2012 @ 11:13:
Je topictitel snap ik niet. Je haalt elke keer maar één vertaling op.

[...]

Ik denk dat hij bedoelt meerdere velden voor één vertaling (titel, omschrijving, etc).

In de tabel b dan welteverstaan.

Idd

[[ti]]

[code]
SELECT p.id AS productid, p.prijs, t_titel.lang, t_titel.content AS titel, t_omschr.content AS omschrijving
FROM bart__producten p
INNER JOIN bart__translations t_titel ON (p.titel_id = t_titel.contentid AND t_titel.lang = 'en')
INNER JOIN bart__translations t_omschr ON (p.omschr_id = t_omschr.contentid AND t_omschr.lang = 'en')
[/code]

[ Voor 8% gewijzigd door [ti] op 25-01-2012 11:23 . Reden: werkt niet :) ]

[HyperioN]

Arjan A schreef op woensdag 25 januari 2012 @ 11:11:
Je datamodel is wel goed, al zou ik de taal ook een getal maken ipv 'en' of 'nl'.

En wat is de reden daarachter? Is dat qua performance beter? Kan me voorstellen dat MySQL makkelijker naar een getal "zoekt" dan een string of enum.

Verder hoef je in een join niet een hele subquery te geven, maar volstaat de tabelnaam.
Dus:

SQL:

JOIN translations b ON a.titel_id = b.contentid AND b.lang = 'en'

Ah, thanks. Wat raar dat ik dat niet besefte

GlowMouse schreef op woensdag 25 januari 2012 @ 11:13:
Je topictitel snap ik niet. Je haalt elke keer maar één vertaling op.

Ik bedoelde inderdaad meerdere vertalingen als in: een vertaling voor titel, omschrijving, etc.

[ti] schreef op woensdag 25 januari 2012 @ 11:16:

code:

SELECT p.id AS productid, p.prijs, t_titel.lang,  t_titel.content AS titel, t_omschr.content AS omschrijving
FROM bart__producten p
INNER JOIN bart__translations t_titel ON (p.titel_id = t_titel.contentid AND t_titel.lang = 'en') 
INNER JOIN bart__translations t_omschr ON (p.omschr_id = t_omschr.contentid AND t_omschr.lang = 'en')

Thanks. Paar naampjes klopten nog niet, maar dat is niet erg.

Op deze manier werkt hij prima:

SQL:

SELECT p.id AS productid, p.prijs, t_titel.lang,  t_titel.content AS titel, t_omschr.content AS omschrijving
FROM bart__producten p
INNER JOIN bart__translations t_titel ON (p.titel_id = t_titel.content_id AND t_titel.lang = 'en') 
INNER JOIN bart__translations t_omschr ON (p.omschr_id = t_omschr.content_id AND t_omschr.lang = 'en')

Daar komt precies hetzelfde result uit als in de topicstart Stuk overzichtelijker zo.

/edit:

[ti] schreef op woensdag 25 januari 2012 @ 11:16:
[code]
SELECT p.id AS productid, p.prijs, t_titel.lang, t_titel.content AS titel, t_omschr.content AS omschrijving
FROM bart__producten p
INNER JOIN bart__translations t_titel ON (p.titel_id = t_titel.contentid AND t_titel.lang = 'en')
INNER JOIN bart__translations t_omschr ON (p.omschr_id = t_omschr.contentid AND t_omschr.lang = 'en')
[/code]

Hoezo denk je dat dat niet werkt? Hier in phpmyadmin werkt het prima; maar misschien zie ik iets over het hoofd?

[ Voor 13% gewijzigd door HyperioN op 25-01-2012 11:29 ]

[[ti]]

Oh, werkt ie wel? Hm, dan is m'n brain query engine 'n beetje van de leg.

Ik dacht dat ie niet ging werken vanwege de inner join dat ie te strict zou zijn, maar ik zit 't allemaal uit m'n hoofd te doen dus even testen ging niet

[Arjan A]

HyperioN schreef op woensdag 25 januari 2012 @ 11:27:
[...]
En wat is de reden daarachter? Is dat qua performance beter?Kan me voorstellen dat MySQL makkelijker naar een getal "zoekt" dan een string of enum.

Dat dus

[Rmg]

SQL:

SET @lang=en;
SELECT p.id AS productid, p.prijs, t_titel.lang,  t_titel.content AS titel, t_omschr.content AS omschrijving
FROM bart__producten p
INNER JOIN bart__translations t_titel ON (p.titel_id = t_titel.content_id AND t_titel.lang = @lang) 
INNER JOIN bart__translations t_omschr ON (p.omschr_id = t_omschr.content_id AND t_omschr.lang = @lang)

[ValHallASW]

Arjan A schreef op woensdag 25 januari 2012 @ 11:37:
[...]

Dat dus

En dat maakt genoeg uit om je SQL+code slechter leesbaar voor te maken? plus: [citation needed]


Volgens mij maakt het namelijk geen flikker uit, zelfs al zou het vergelijken van een string trager zijn. Als je namelijk een index (content_id, lang) hebt dan moet je een whopping {aantal talen} string comparisons uitvoeren.

Er is maar één reden om een string door een getal te vervangen, en dat is normalisatie - maar daar wordt het a) niet sneller van en b) levert het je hier niets op (de use case 'alle vertalingen die eerst Engels waren moeten nu Pietlutters heten' bestaat niet echt, tenzij je later wilt splitsen tussen en_US en en_UK - maar again, dat is een eenmalige actie)

[ Voor 49% gewijzigd door ValHallASW op 25-01-2012 11:53 ]

[HyperioN]

Rmg schreef op woensdag 25 januari 2012 @ 11:37:

SQL:

SET @lang=en;
SELECT p.id AS productid, p.prijs, t_titel.lang,  t_titel.content AS titel, t_omschr.content AS omschrijving
FROM bart__producten p
INNER JOIN bart__translations t_titel ON (p.titel_id = t_titel.content_id AND t_titel.lang = @lang) 
INNER JOIN bart__translations t_omschr ON (p.omschr_id = t_omschr.content_id AND t_omschr.lang = @lang)

Gaaf! Weer wat geleerd. (Moesten nog wel even quotes om 'en' heen )

[RobIII]

HyperioN schreef op woensdag 25 januari 2012 @ 12:07:
[...]

(Moesten nog wel even quotes om 'en' heen )

Neen; wat je daar ziet is een Parametrized Query

[ Voor 3% gewijzigd door RobIII op 25-01-2012 12:17 ]

[Woy]

RobIII schreef op woensdag 25 januari 2012 @ 12:17:
[...]

Neen; wat je daar ziet is een Parametrized Query

Neen; wat je daar ziet is een stukje T-Sql , maar met Parametrized Queries is het natuurlijk eenvoudiger om vanaf de "client" de taal te kunnen kiezen

[RobIII]

Woy schreef op woensdag 25 januari 2012 @ 12:30:
[...]

Neen; wat je daar ziet is een stukje T-Sql , maar met Parametrized Queries is het natuurlijk eenvoudiger om vanaf de "client" de taal te kunnen kiezen

* RobIII wrijft de poep uit z'n ogen* Ik had regel 1 gemist (En daar moeten inderdaad quotes om 'en' heen).

[ Voor 9% gewijzigd door RobIII op 25-01-2012 12:47 ]