[SQL] OR's in bepaalde volgorde uitvoeren (FIRST() functie?)

[Skit3000]

Voor een grote website ben ik aan het kijken of er wat geoptimaliseerd kan worden met betrekking tot het vertalen van velden en teksten. Eerst even de twee tabellen uiteen zetten.

[b][u]LANGUAGES[/u][/b]
[b][u]language_id[/u][/b]	[b][u]language_name[/u][/b]
1		Nederlands
2		Engels
3		Frans
4		Duits

[b][u]STRINGS[/u][/b]
[b][u]string_id[/u][/b]	[b][u]language_id[/u][/b]	[b][u]string_text[/u][/b]
1		3		string_1_frans
1		1		string_1_nederlands
2		4		string_2_duits
3		2		string_3_engels
3		1		string_3_nederlands
3		3		string_3_frans
4		2		string_4_engels
4		4		string_4_duits

Ik wil nu van elke strings.string_id één terughebben (dus GROUP BY string_id), maar dan wel als het kan eerst degene met language_id 2 en anders language_id 3. Mochten die er allebei niet zijn, moet een willekeurige andere uitgekozen worden.

Tot nu toe heb ik dit opgelost door gewoon alles op te halen en via PHP met een mooie loop bovenstaande uitgevoerd. Dit wordt echter veel te langzaam (denk aan 10.000+ rijen) dus ik vraag me af of het niet ook met MySQL zelf mogelijk is.

In het ideale geval zou een query er volgens mij zo uit moeten zien:

   SELECT string_id, string_text
   FROM strings
   WHERE language_id = FIRST(2, 3, 1, 4)

Op onderstaande manier heb ik het geprobeerd met Jip-en-Janneke-code, maar dan pikt die steeds de string_id met de laagste language_id uit, ook al zet je eerst een andere language_id voorop:

   SELECT string_id, string_text
   FROM strings
   WHERE language_id = 2
      OR language_id = 3
      OR language_id = 1
      OR language_id = 4
   GROUP BY string_id

Dit gaf dus bij string_id's waar je als language_id's 1, 2 en 3 hebt dus standaard degene met de waarde 1 terug in plaats van 2 of 3 als die er ook waren.

Iets anders wat ik heb geprobeerd is met UNION SELECT eerst alles zo te sorteren dat de language_id's die ik hebben wilde bovenaan stonden en dan daarna met GROUP BY string_id de bovenste juiste resultaten gepakt. Maarrrrrr, de server met de website heeft UNION SELECT uitgeschakeld staan om mogelijke SQL-inject gevaren af te wenden dus dat feest gaat niet door.

Weet iemand hoe ik dit probleem met MySQL kan oplossen?

[jvdmeer]

Ik zou een extra veld toevoegen aan de talen tabel met daarin de volgorde van opvragen. Dan kan je daarop sorteren en de MIN of MAX gebruiken.

[_Sunnyboy_]

Zoals jvdmeer hierboven zegt kan het als je een priority kolom aan de languages tabel toevoegt die aangeeft welke taal je bij voorkeur terugkrijgt. Als die kolom bestaat dan levert onderstaande query volgens mij op wat jij wil:

SQL:

SELECT s.string_id, s.language_id, s.string_text
FROM strings s
WHERE s.language_id = 
( 
    SELECT l.language_id
    FROM languages AS l
    WHERE l.priority = 
    ( 
        SELECT MIN( l2.priority ) 
        FROM strings AS s2, languages AS l2
        WHERE s2.language_id = l2.language_id
        AND s2.string_id = s.string_id 
    ) 
) 

Bovenstaande query werkt bij mij op MySQL 5.0.
De query bevat wel 2 geneste subqueries, dus in hoeverre dit sneller is dan je huidige oplossing bij een flinke database durf ik niet te zeggen.

[ Voor 6% gewijzigd door _Sunnyboy_ op 01-08-2007 22:28 ]

[BikkelZ]

Ik zeg dat je het beste in je tabel LANGUAGES een kolom `pickorder` kunt aanmaken waar een arbitrair en UNIQUE nummer staat waarmee je de talen sorteert. Dat kost je een extra JOIN en misschien een paar bytes, en dat is het beste om te gebruiken. PRIMARY KEYs voor dat soort zaken gaan misbruiken werkt gewoon niet goed, want die language_id's zitten waarschijnlijk al overal doorheen.

--------

jvdmeer zei het al.....

[ Voor 4% gewijzigd door BikkelZ op 01-08-2007 23:56 ]

[ACM]

Het kan wel met een vrij eenvoudige subquery. Deze werkt bij mij:

SQL:

SELECT string_id, language_id, string_text
FROM strings o
WHERE language_id = (
            SELECT i.language_id FROM 
              strings i
            WHERE i.string_id = o.string_id
            ORDER BY
                CASE language_id WHEN 2 THEN 1 
                                           WHEN 3 THEN 2
                                           WHEN 1 THEN 3
                                           WHEN 4 THEN 4
                                           ELSE 5
                 END
                LIMIT 1)

Als je nog met MySQL 4.1 opgescheept zit wordt het een stuk lastiger... want die accepteerd die limit niet. En als je bovenstaande pickorder-oplossing doet kan je mijn order by natuurlijk vervangen door een ORDER BY pickorder, scheelt je weer een extra subquery

[ Voor 9% gewijzigd door ACM op 02-08-2007 10:22 ]

[ATS]

Er zijn al een paar oplossingen gegeven, maar daarbij is geen rekening gehouden met de vraag of die volgorde per pageview anders moet zijn of niet. Eigenlijk lijkt mij dat wel voor de hand liggen, toch? Dat wil zeggen: voor een Nederlander heb je als volgorde misschien 1, 2, 4, 3, maar voor een Duitser, een Amerikaan of een Fransoos ligt dat natuurlijk anders :-)
Als dat het geval is, kan je niet gewoon een extra kolom gebruiken in je languages tabel. Je zal dan denk ik een extra tabel moeten toevoegen waarin je voor elke taal de volgorde van de voorkeurstalen invoegt. Die kan je vervolgens joinen.

[ACM]

ATS schreef op donderdag 02 augustus 2007 @ 10:55:
Als dat het geval is, kan je niet gewoon een extra kolom gebruiken in je languages tabel. Je zal dan denk ik een extra tabel moeten toevoegen waarin je voor elke taal de volgorde van de voorkeurstalen invoegt. Die kan je vervolgens joinen.

Dan heb je over mijn oplossing heen gekeken... Het enige wat je hoeft te weten is de volgorde, maar die hoef je niet mee te joinen, dus hoef je er geen aparte voglordetabellen op na te houden, die kan je gewoon door een andere CASE neer te zetten aanpassen in de query. Uiteraard moet je het wel ergens opslaan (bijvoorbeeld bij de voorkeuren van je gebruiker), maar je hoeft niet per se 24 verschillende tabellen bij te houden met de mogelijke volgordes per taalvoorkeur die er in theorie mogelijk zijn voor bij de gebruikers.

[BikkelZ]

Het is zeker een mooie oplossing die je aandraagt, misschien dat het in dit geval vanwege de leesbaarheid beter is met een geindexeerde CHAR(2) code-kolom te werken zodat je niet tegen van die rare arbitraire nummers aan zit te kijken, dat is gewoon vragen om problemen bij iedere aanpassing.

[Skit3000]

ACM schreef op donderdag 02 augustus 2007 @ 10:18:
Het kan wel met een vrij eenvoudige subquery. Deze werkt bij mij:

SQL:

SELECT string_id, language_id, string_text
FROM strings o
WHERE language_id = (
            SELECT i.language_id FROM 
              strings i
            WHERE i.string_id = o.string_id
            ORDER BY
                CASE language_id WHEN 2 THEN 1 
                                           WHEN 3 THEN 2
                                           WHEN 1 THEN 3
                                           WHEN 4 THEN 4
                                           ELSE 5
                 END
                LIMIT 1)

Als je nog met MySQL 4.1 opgescheept zit wordt het een stuk lastiger... want die accepteerd die limit niet. En als je bovenstaande pickorder-oplossing doet kan je mijn order by natuurlijk vervangen door een ORDER BY pickorder, scheelt je weer een extra subquery

Dat is precies de query die ik nog heb inderdaad zeg Alleen je raad het al, er draait MySQL 4.1 die de LIMIT 1 niet aan kan. Op mijn testmachine werkt het echter voortreffelijk!

Met MAX() en MIN() kan je de hoogste en de laagste veldwaarde te pakken krijgen, maar is er naast LIMIT 1 ook een andere manier om de eerste rij te vangen zodat ik de query werkend kan krijgen op MySQL 4.1?

[ACM]

Skit3000 schreef op donderdag 02 augustus 2007 @ 13:34:
Met MAX() en MIN() kan je de hoogste en de laagste veldwaarde te pakken krijgen, maar is er naast LIMIT 1 ook een andere manier om de eerste rij te vangen zodat ik de query werkend kan krijgen op MySQL 4.1?

Sure, maar dan moet je gaan knoeien met het dubbel plaatsen van die CASE, waardoor je kans loopt dat je query minder efficient wordt ivm de mogelijkheid dat ie bij die language_id van de buitenste query geen index meer kan gebruiken, voor zover dat eerder nog wel kon:

SQL:

SELECT string_id, language_id, string_text
FROM strings o
WHERE 
    CASE language_id WHEN 2 THEN 1 
                     WHEN 3 THEN 2
                     WHEN 1 THEN 3
                     WHEN 4 THEN 4
                     ELSE 5
     END = (
            SELECT MIN(CASE language_id WHEN 2 THEN 1 
                                 WHEN 3 THEN 2
                                 WHEN 1 THEN 3
                                 WHEN 4 THEN 4
                                 ELSE 5
                 END)
            FROM 
              strings i
            WHERE i.string_id = o.string_id)

[XiniX88]

Even kijken... Als ik het goed begrijp wil je dus eerst een primaire taal, dan secudaire en anders een random. Wat je kunt doen, en volgensmij moet dit zelfs werken met MySQL 3.23 is eerst alles selecteren (dat is dus eerst zorgen dat je alle talen hebt (= tabel l, GROUP by l.string_id)) + de primaire (= l1) en secudaire (=l2) taal...

SQL:

SELECT l.string_id, IF(l1.string_id,l1.string_text,IF(l2.string_id,l2.string_text, l.string_text)) AS string
FROM `strings` AS l
LEFT JOIN strings AS l1 ON l1.language_id = 2 AND l.string_id = l1.string_id
LEFT JOIN strings AS l2 ON l2.language_id = 3 AND l.string_id = l2.string_id
GROUP by l.string_id

met een IF kijken welke er is ingevuld.

EDIT om mijn uitleg te verduidelijken, je krijgt nu:
Je selecteerd 3 talen,
tabel strings AS l selecteerd alle string_id's en pakt 1 random string (ipv alle door de GROUP BY)
tabel strings AS l1 selecteerd alleen alles waar language_id 2 is
tabel strings AS l2 selecteerd alleen alles waar language_id 3 is

Vervolgens kijk je welk van de 3 is ingevuld, dus eerst de primaire taal (l1) dan de secudaire taal (l2) en mocht dat nog niets opleveren de random (en altijd ingevulde taal) l

[ Voor 48% gewijzigd door XiniX88 op 02-08-2007 16:12 ]

[Skit3000]

ACM, echt helemaal top en dat is echt een geweldig staaltje SQL werk!

XiniX, jij helemaal bedankt, jouw query doet 't 'm in 0.0001 seconde (sneller kan ik niet meten) waar die van ACM (alhoewel ik die wel een stuk duidelijker vind qua uitleg ) er 0.0283 seconde over doet (en na herhaaldelijk opnieuw uitvoeren valt dat terug naar ongeveer de helft van die tijd per keer).

Edit: XiniX, sorry voor dat over die uitleg, zo kort als dat je 'm nu hebt valt er weinig niet aan te begrijpen En die snelheidsvermeerdering is ook aardig meegenomen

Edit: En extra dank voor de extra extra uitleg

[ Voor 22% gewijzigd door Skit3000 op 02-08-2007 16:09 ]