GROUP BY over de UNION resultset

[radem205]

Hey,

Om de actieve topics te krijgen van een forum gebruik ik onderstaande query:

SQL:

SELECT
                            topicid,
                            lidid,
                            gebruikersnaam,
                            topictitel,
                            datum
                        FROM
                            (
                            (                           
                                SELECT
                                    leden.id AS lidid,
                                    leden.gebruikersnaam AS gebruikersnaam,
                                    forum_topics.id AS topicid,
                                    forum_topics.titel AS topictitel,
                                    forum_topics.datum
                                FROM
                                    forum_topics
                                LEFT JOIN
                                    leden
                                ON
                                    leden.id = forum_topics.lidid
                            )
                            UNION
                            (
                                SELECT
                                    leden.id AS lidid,
                                    leden.gebruikersnaam AS gebruikersnaam,
                                    forum_topics.id AS topicid,
                                    forum_topics.titel AS topictitel,
                                    forum_reacties.datum
                                FROM
                                    forum_reacties
                                LEFT JOIN
                                    leden
                                ON
                                    leden.id = forum_reacties.lidid
                                INNER JOIN
                                    forum_topics
                                ON
                                    forum_topics.id = forum_reacties.topicid
                             )
                             ) AS uniontable
                        GROUP BY
                            topicid,
                            lidid,
                            gebruikersnaam,
                            topictitel,
                            datum
                        ORDER BY
                            datum DESC

Wwanneer ik de SELECT statement over de gehele UNION zet (zoals hierboven) en de GROUP BY toevoeg dan werkt de GROUP BY blijkbaar niet goed. Ik krijg namelijk nog dubbele topicid's terug in de resultaten.

Is dit überhaupt een goede methode om actieve topics weer te geven (of gaat dit heel traag worden na een tijdje)? Zo ja, hoe kan het dat de GROUP BY niet correct werkt?

[ Voor 7% gewijzigd door radem205 op 14-03-2010 23:15 ]

[_eXistenZ_]

Je krijgt wss hetzelfde topicid terug met een ander lidid? Dit lijkt me ook gewoon goed en mogelijk, je groupt niet op je topicid.

[ Voor 6% gewijzigd door _eXistenZ_ op 14-03-2010 23:39 ]

[radem205]

Ja dat klopt, maar wanneer ik group op alleen topicid dan krijg ik precies dezelfde resultaten.

[RobIII]

radem205 schreef op zondag 14 maart 2010 @ 23:46:
Ja dat klopt, maar wanneer ik group op alleen topicid dan krijg ik precies dezelfde resultaten.

Dat kan (eigenlijk) niet eens; je dient op alle velden die niet in aggregate functies zijn opgenomen te groupen: Hoe werkt dat GROUP BY nu eigenlijk?. Dat MySQL het niet keihard afkeurt is andere koek.

[ Voor 62% gewijzigd door RobIII op 14-03-2010 23:57 ]

[radem205]

Ik weet dat het eigenlijk niet kan / mag, en daarom wil ik het ook niet doen. Maar hoe zorg ik er dan voor dat. dubbele topicid's worden verwijderd en de hoogste bijbehorende datum in de resultaten komt (dit kan met de aggregate functie MAX naar mijn weten)?

[Gomez12]

Dat kan met :

code:

SELECT 
                            topicid, 
                            MAX(lidid), 
                            MAX(gebruikersnaam), 
                           topictitel, 
                            MAX(datum)
...
group by topicid,topictitel

oftewel alles wat niet in je group by moet komen moet in een aggegrate function zitten.
Topic-id en topictitel lijkt me een 1-1 relatie dus die kunnen wel gezamelijk in je group by.
Maar voor de rest zie ik zo snel ( zonder verdere info ) niets wat zonder aggegrate function zou kunnen.

Mysql staat je wel toe om gebruikersnaam en lidid etc niet te aggregeren, maar ik zou dit echt afraden, je resultaten zullen enigszins onvoorspelbaar worden ( in het begin lijkt het wel voorspelbaar ) en je code gaat op elk ander dbms gewoon fouten genereren...

[_eXistenZ_]

Zonder dit topic te kapen: is er een manier om MySQL wel strikt te krijgen? Geen flauwe dingen roepen zoals je eigen versie compilen.

[cariolive23]

_eXistenZ_ schreef op maandag 15 maart 2010 @ 02:38:
Zonder dit topic te kapen: is er een manier om MySQL wel strikt te krijgen? Geen flauwe dingen roepen zoals je eigen versie compilen.

Kapot schieten en vervangen door bv. PostgreSQL of Firebird is de beste optie, maar je zou eventueel ook een betere SQL mode in kunnen stellen:
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

Dit betekent niet dat daarmee de boel altijd betrouwbaar is, alles en iedereen kan voor zijn/haar connectie deze instellingen weer aanpassen/naar de bliksem helpen. En deze rechten zijn niet in te trekken, alles en iedereen heeft automatisch deze rechten. Zelfs unique en foreign key constraints zijn "voor het gemak" uit te zetten...

[ Voor 4% gewijzigd door cariolive23 op 15-03-2010 08:14 . Reden: url's toegevoegd ]

[Janoz]

Maar goed, je werkelijke probleem is natuurlijk dat je bedacht had het eerste bericht in een topic iets anders is dan alle daarop volgende reacties waardoor je dergelijke kromme queries nodig hebt.

[radem205]

Janoz schreef op maandag 15 maart 2010 @ 09:45:
Maar goed, je werkelijke probleem is natuurlijk dat je bedacht had het eerste bericht in een topic iets anders is dan alle daarop volgende reacties waardoor je dergelijke kromme queries nodig hebt.

Ik begrijp niet goed wat je bedoeld. Wil je hiermee zeggen dat op deze manier actieve topics uit de database halen een ongebruikelijke methode is?

[Janoz]

Nee, ik wil niet zeggen dat dit een vreemde manier is om je data uit de database te halen. Wat ik wil zeggen is dat je een nogal onhandige manier gekozen hebt om je data in de database te stoppen waardoor je nu unions nodig hebt om je topic uit de database te halen.

Wat is het verschil tussen een topic start en alle daarop volgende reacties?

[radem205]

Janoz schreef op maandag 15 maart 2010 @ 10:50:
Nee, ik wil niet zeggen dat dit een vreemde manier is om je data uit de database te halen. Wat ik wil zeggen is dat je een nogal onhandige manier gekozen hebt om je data in de database te stoppen waardoor je nu unions nodig hebt om je topic uit de database te halen.

Wat is het verschil tussen een topic start en alle daarop volgende reacties?

Ik kan hier zeker in komen, maar is dit daadwerkelijk een betere opzet voor een forum? Je krijgt dan veel null values in je tabel en het wordt vrij onoverzichtelijk in je database beheer.
Voordat ik het daadwerkelijk allemaal ga omgooien zou ik graag een second opinion hebben.

[Janoz]

Je krijgt dan veel null values in je tabel en het wordt vrij onoverzichtelijk in je database beheer.

Ik begrijp niet waar de null values vandaan komen. Ook denk ik niet dat de boel onoverzichtelijk wordt in je database beheer. Maar over dat laatste punt. Voor je database ontwerp is de requirement 'het moet goed leesbaar zijn in een database managment tool' wel de minst belangrijke. Het enige moment waar die requirement om de hoek komt kijken is wanneer je je kolommen een begrijpelijke naam geeft.

Maar terugkomend op de null values. Wat voor andere database structuur heb jij voor ogen die meer null values zou bevatten?

[radem205]

Janoz schreef op maandag 15 maart 2010 @ 12:43:
[...]

Ik begrijp niet waar de null values vandaan komen. Ook denk ik niet dat de boel onoverzichtelijk wordt in je database beheer. Maar over dat laatste punt. Voor je database ontwerp is de requirement 'het moet goed leesbaar zijn in een database managment tool' wel de minst belangrijke. Het enige moment waar die requirement om de hoek komt kijken is wanneer je je kolommen een begrijpelijke naam geeft.

Maar terugkomend op de null values. Wat voor andere database structuur heb jij voor ogen die meer null values zou bevatten?

Ik had in gedachte om dus één tabel "forum_posts" te gebruiken met de volgende velden:

id

cat

topicid

lidid

titel

bericht

sticky

gesloten

datum

Bij een nieuw topic dienen dus waarden ingevuld te worden in "id" (autoincrement), "cat","lidid","titel","bericht","sticky","gesloten" en "datum".
Bij een reactie hoeft alleen "id", "topicid", "lidid", "bericht" en "datum" ingevuld te worden toch?
Dan zou je de overige velden een waarde null kunnen meegeven.

Of is er een handigere manier?

Edit: Word jouw methode hier op GOT ook gebruikt?

[ Voor 3% gewijzigd door radem205 op 15-03-2010 12:49 ]

[Janoz]

Nee, dat was niet de oplossing die ik bedoelde.

Mijn vraag was ook niet "wat is het verschil tussen een topic en een reactie". We zijn het er allebei over eens dat dat twee verschillende dingen zijn. Mijn vraag was, wat is het verschil tussen het startbericht en alle daarop volgende reacties?

[radem205]

Janoz schreef op maandag 15 maart 2010 @ 12:58:
Nee, dat was niet de oplossing die ik bedoelde.

Mijn vraag was ook niet "wat is het verschil tussen een topic en een reactie". We zijn het er allebei over eens dat dat twee verschillende dingen zijn. Mijn vraag was, wat is het verschil tussen het startbericht en alle daarop volgende reacties?

Het verschil tussen het startbericht en de daarop volgende reacties is dat het startbericht een titel heeft, een aantal andere waarden zoals: sticky, status (gesloten), etc.

Maar ik snap niet helemaal waar je dan naar toe wilt, kan je mij dat uitleggen?

[Janoz]

Heeft het startbericht een titel en een aantal andere waarden zoals: sticky, status (gesloten), etc? Of heeft een topic dat?

[ Voor 3% gewijzigd door Janoz op 15-03-2010 13:10 ]

[radem205]

Janoz schreef op maandag 15 maart 2010 @ 13:09:
Heeft het startbericht een titel en een aantal andere waarden zoals: sticky, status (gesloten), etc? Of heeft een topic dat?

Op zich heeft een topic dat . Dan zal je dus een tabel met topics moeten aanmaken die de titel, sticky, gesloten, etc. bevat en in de tabel "posts" alle post plaatsen inclusief de startpost (die dan weer refereren aan een topic).

Ik begin te begrijpen waar je naar toe wilt

[ Voor 3% gewijzigd door radem205 op 15-03-2010 13:14 ]

[Janoz]

Kijk vervolgens eens hoe je query uit dit topic er uit zou zien wanneer je een dergelijke database structuur zou hebben. Kijk vervolgens ook eens hoe de code voor het renderen van een topic er uit komt te zien. Kijk daarna ook nog eens naar de hoeveelheid code die je kunt hergebruiken tussen je 'start topic' en je 'reageer' pagina's.

Je startpost is eigenlijk niks anders dan de eerste reactie in een topic.

[LauPro]

Zoekt de TS niet gewoon een UNION DISTINCT of/en UNION ALL?

[Janoz]

Nee, de topicstarter zoekt eigenlijk gewoon een beter uitgenormaliseerd database model

[radem205]

Ja helemaal goed! Dit is een veel betere methode. Scheelt een hoop geklooi. Thanks!

[sopsop]

@LauPro: Nee, aangezien hij het id ook opvraagt zullen er geen dubbele records voorkomen. Daarbij is UNION DISTINCT de default behaviour van UNION en zorgt ALL er nou juist voor dat ook de dubbele records worden meegegeven.

Maar zoals al aangehaald: over het algemeen is het moeten gebruiken van een UNION het gevolg van een fout in je datamodel of een verzoek om archiveringsdata in een query mee te nemen.

[ Voor 31% gewijzigd door sopsop op 15-03-2010 13:33 ]

[Mr_gadget]

Janoz schreef op maandag 15 maart 2010 @ 13:28:
Nee, de topicstarter zoekt eigenlijk gewoon een beter uitgenormaliseerd database model

Weet je hier toevallig een goede website voor met cases bijvoorbeeld?

[Alain]

Janoz heeft 100% gelijk, maar ik vond het wel een interessante case. Ik kan het niet reproduceren:

SQL:

DROP TABLE IF EXISTS A;
DROP TABLE IF EXISTS B;

CREATE TABLE A (
  id INT,
  name VARCHAR(255));
  
CREATE TABLE B (
  id INT,
  name VARCHAR(255));

INSERT INTO A VALUES (1, 'name 1'), (2, 'name 2');
INSERT INTO B VALUES (1, 'name 1'), (2, 'name 2'), (3, 'name 3');

SELECT id, name FROM A 
UNION
SELECT id, name FROM B;

SELECT id, name FROM (
(SELECT id, name FROM A)
UNION
(SELECT id, name FROM B)
) AS CombinedTable
GROUP BY id, name;

Uitkomst in beide gevallen:

code:

+------+--------+
| id   | name   |
+------+--------+
|    1 | name 1 |
|    2 | name 2 |
|    3 | name 3 |
+------+--------+

[sopsop]

Dat was toch ook het 'probleem' met mysql?! Dat de group by zonder agregate onvoorspelbare resultaten geeft?! Terwijl dit gewoon als foutieve querie bestempeld zou moeten worden.

[ Voor 26% gewijzigd door sopsop op 16-03-2010 08:12 ]

[radem205]

Om geen nieuwe topic te hoeven openen voor een relatief kleine vraag doe ik het even via deze weg:

Na het wijzigen van een bericht op een forum wil ik achterhalen op welke pagina het bericht binnen een topic staat, om zo na het wijzigen de gebruiker rechtstreeks door te kunnen sturen naar zijn / haar bericht op de goede pagina.

Is er een manier in mysql om te achterhalen op welke plaats een bepaalde record staat binnen bepaalde voorwaarden (WHERE) ?
Dus wanneer een bericht de 20e in de reeks (topic) is en ik laat 15 berichten per pagina zien dan moet de gebruiker na het wijzigen van het 20e bericht naar pagina 2 binnen het topic gestuurd worden.

Edit: sorry, ik had opeens een ingeving waar ik het mee kan oplossen. Excuus

[ Voor 5% gewijzigd door radem205 op 19-03-2010 17:22 ]