[SQL]GROUP BY met custom ORDER BY *

[mithras]

Ik heb een database structuur waarbij er een enkele pagina in meerdere talen kan bestaan:

code:

pages
 id - int(4)
 node_id - int(4)
 name - varchar(255)
 lang - varchar(4)
 menu - int(4)

De name + lang is unique. Er is een many:1 relatie met een node. Er kunnen dus meerdere pagina's onder een node hangen. Nu heeft de gebruiker een taal geselecteerd en wil ik alle pagina's van een node selecteren, maar wel binnen die taal. Mocht de taal niet aanwezig zijn, is er altijd een backup taal waarin de pagina verplicht aanwezig is.

Ik deed voorheen (zonder taal-gebeuren):

SQL:

SELECT P.*
FROM node_nodes AS N
LEFT JOIN node_pages AS P
  ON N.id=P.node_id
WHERE N.name='leuke_pagina_naam'
ORDER BY P.menu

De naam is gebruikersinput. Nu selecteer ik vervolgens pagina's en sorteer ze manueel (eerst geselecteerde taal, dan backuptaal):

SQL:

SELECT P.*
FROM node_nodes AS N
LEFT JOIN node_pages AS P
  ON N.id=P.node_id
WHERE N.name='leuke_pagina_naam'
AND P.lang in ('en', 'nl')
ORDER BY P.menu, P.lang='en' DESC, P.lang='nl' DESC

Nu krijg ik dus mooi alle pagina's van die node, geselecteerd op eerst de geselecteerde taal (en) en dan de backup taal (nl).

Ik krijg nu dus wel alle pagina's binnen van een node. Ik heb een GROUP BY name geprobeerd, maar dan neemt hij de ingestelde ORDER BY niet mee. Hoe kan ik, als er dubbele pagina's zijn (dus van meerdere talen) deze groeperen?

Een distinct werkt natuurlijk niet, omdat de rij niet uniek is (de taal is mee geselecteerd). Een group by houdt geen rekening met order by. Er zijn wel meer topics geweest over group by + order by, maar dat ging altijd over "automatische" order by queries. En dit is een beetje een apart geval...

[pedorus]

Bedenk dat er 2 situaties zijn dat je de pagina wil:

1. De taal van de pagina is EN
2. De taal van de pagina is NL, en de pagina bestaat niet in EN (gebruik iets als not in, not exists, left join is null, etc)

Niet oplossen met order by, of group by lijkt mij.

Select * is vaak een slecht idee, je haalt te veel en/of onbekende data op.

[.oisyn]

Ik snap het niet helemaal, hoe weet je van twee pagina's dat ze over dezelfde pagina gaan, maar in verschillende talen geschreven zijn? Is node_pages.name dan gelijk? En node_pages.name is vermoed ik verder ongerelateerd aan node_nodes.name?

Als bovenstaande aannames correct zijn, dan zou je dus gaan groeperen op P.name. Maar dan weet je nog niets, want dan moet je een aggregate gebruiken voor P.id en voor P.lang, waar je neem ik aan ook in geïnteresseerd bent. En dan wil je het P.id waar P.lang = 'en', en anders het P.id waar P.lang = 'nl', maar zo'n aggregate bestaat natuurlijk niet.

Wat je volgens mij wilt, is gewoon een subquery waarbij je per row de juiste taal selecteert:

SQL:

SELECT P.* 
FROM node_nodes AS N 
LEFT JOIN node_pages AS P 
  ON N.id=P.node_id 
WHERE N.name='leuke_pagina_naam' 
AND P.id IN (
    SELECT P2.id
    FROM node_pages P2
    WHERE P2.name = P.name
    ORDER BY P2.lang='en' DESC, P2.lang='nl' DESC
    LIMIT 1
) 
ORDER BY P.menu

Hoe de performance daarvan is mag je zelf uitvinden

[mithras]

pedorus schreef op dinsdag 28 oktober 2008 @ 13:39:
Bedenk dat er 2 situaties zijn dat je de pagina wil:

1. De taal van de pagina is EN
2. De taal van de pagina is NL, en de pagina bestaat niet in EN (gebruik iets als not in, not exists, left join is null, etc)

Niet oplossen met order by, of group by lijkt mij.

Eigenlijk zijn er de volgende situaties:

1. Gebruiker bezoekt website in standaardtaal (pagina is dus 100% zeker aanwezig)
2. Gebruiker bezoekt website in niet-standaardtaal en pagina bestaat in die taal (selecteer die taal)
3. Gebruiker bezoekt website in niet-standaardtaal en pagina bestaat niet in die taal (selecteer pagina in backup-taal)

Select * is vaak een slecht idee, je haalt te veel en/of onbekende data op.

Om jullie niet te vermoeien heb ik een uitgeklede versie gemaakt. Ik heb de queries in dat opzicht wel geoptimaliseerd

.oisyn schreef op dinsdag 28 oktober 2008 @ 13:40:
Ik snap het niet helemaal, hoe weet je van twee pagina's dat ze over dezelfde pagina gaan, maar in verschillende talen geschreven zijn? Is node_pages.name dan gelijk? En node_pages.name is vermoed ik verder ongerelateerd aan node_nodes.name?

Er is een node, bijvoorbeeld "opleidingen". Daaronder zitten pagina's die een node_id hebben van die opleidingen. De namen van die pagina's zijn bijvoorbeeld "informatica", "werktuigbouwkunde", "nederlands".

Je kan dus een pagina (node_pages) hebben met node_id = 1 ("Opleidingen"), name = "informatica" en lang="nl". Daarnaast bestaat nog een pagina met node_id = 1, name="informatica" en lang="en".

De combinatie node_id + name + lang maakt het record uniek.

offtopic:
Ik heb dus een niet-genormaliseerde tabel (dat weet ik), maar die keuze is omdat het zo simpeler is. Waarom twee tabellen als alleen de data node_id, name, user_id en menu (allemaal int(4)) dubbelop is? Verder verhelpt het normaliseren afaik dit probleem niet, mocht iemand dat willen opmerken.

Als bovenstaande aannames correct zijn, dan zou je dus gaan groeperen op P.name. Maar dan weet je nog niets, want dan moet je een aggregate gebruiken voor P.id en voor P.lang, waar je neem ik aan ook in geïnteresseerd bent. En dan wil je het P.id waar P.lang = 'en', en anders het P.id waar P.lang = 'nl', maar zo'n aggregate bestaat natuurlijk niet.

Dat klopt in zoverre dat eigenlijk het id verder niet interessant is voor mij. Het identificeert een (node_id + name + lang) uniek record. Ik krijg als user input een node_name en lang en dus page.names teruggeven.

Alleen jammer dat zoiets niet bestaat...

Wat je volgens mij wilt, is gewoon een subquery waarbij je per row de juiste taal selecteert:

SQL:

SELECT P.* 
FROM node_nodes AS N 
LEFT JOIN node_pages AS P 
  ON N.id=P.node_id 
WHERE N.name='leuke_pagina_naam' 
AND P.id IN (
    SELECT P2.id
    FROM node_pages P2
    WHERE P2.name = P.name
    ORDER BY P2.lang='en' DESC, P2.lang='nl' DESC
    LIMIT 1
) 
ORDER BY P.menu

Hoe de performance daarvan is mag je zelf uitvinden

Tja, dat kan ik ook wel bedenken zonder hem uit te voeren

Op dit moment selecteer ik dus alle pagina's en ga in php maar filteren op unieke names. Dat is denk ik nog sneller dat dit. Maar bedankt voor je input

[Verwijderd]

Iets als dit is misschien ook nog een optie:

SQL:

SELECT P1.id, P1.node_id, P1.name, P1.menu
FROM node_nodes AS N1
LEFT JOIN node_pages AS P1
  ON N1.id=P1.node_id
WHERE N1.name='leuke_pagina_naam'
AND P1.lang='en'
UNION
SELECT P2.id, P2.node_id, P2.name, P2.menu
FROM node_nodes AS N2
LEFT JOIN node_pages AS P2
  ON N2.id=P2.node_id
WHERE N2.name='leuke_pagina_naam'
AND P2.lang='nl'
AND NOT EXISTS (SELECT name FROM node_pages
                WHERE node_id=P2.node_id
                AND name=P2.name
                AND lang='en')
ORDER BY 4

Hoe MySQL hiermee omgaat weet ik niet (maar ik heb wel een vermoeden ), maar voor een database die goed met meerdere indexen in 1 execution plan overweg kan is 't geen probleem.

Edit: Dit geldt alleen wanneer node_pages.name voor dezelfde node_pages bij die node_id gelijk zijn, maar daar ga ik wel vanuit. node_pages.id is denk ik een auto_increment / identity field, en daar kun je niet meerdere records op matchen.

[ Voor 13% gewijzigd door Verwijderd op 28-10-2008 18:43 ]

[pedorus]

Verwijderd schreef op dinsdag 28 oktober 2008 @ 18:34:
Iets als dit is misschien ook nog een optie:

SQL:

SELECT P1.id, P1.node_id, P1.name, P1.menu
FROM node_nodes AS N1
LEFT JOIN node_pages AS P1
  ON N1.id=P1.node_id
WHERE N1.name='leuke_pagina_naam'
AND P1.lang='en'
UNION
SELECT P2.id, P2.node_id, P2.name, P2.menu
FROM node_nodes AS N2
LEFT JOIN node_pages AS P2
  ON N2.id=P2.node_id
WHERE N2.name='leuke_pagina_naam'
AND P2.lang='nl'
AND NOT EXISTS (SELECT name FROM node_pages
                WHERE node_id=P2.node_id
                AND name=P2.name
                AND lang='en')
ORDER BY 4

-->

SQL:

SELECT P1.id, P1.node_id, P1.name, P1.menu
FROM node_nodes AS N1
LEFT JOIN node_pages AS P1
  ON N1.id=P1.node_id
WHERE N1.name='leuke_pagina_naam'
AND (P1.lang='en'
    OR (P2.lang='nl'
        AND NOT EXISTS (SELECT name FROM node_pages
                WHERE node_id=P2.node_id
                AND name=P2.name
                AND lang='en')))
ORDER BY 4

(en dan de namen nog even goed + opmaak beter)