[sql] Inner join, order én group by - Softwareontwikkeling

zondag 4 november 2007 13:31

Acties:

StephanVierkant

Topicstarter

Ik heb een klein forumpje met twee tabellen:
forum

id
title

forummsg

id
topicid
bericht
user
timestamp (time())

Ik heb nu een overzicht met de laatste 10 topics:

SQL:

SELECT forum.id, title, user, timestamp, count(forummsg.id) AS aantal FROM forum
    INNER JOIN forummsg
        ON forummsg.topicid = forum.id
GROUP BY forummsg.topicid
ORDER BY forummsg.timestamp ASC
LIMIT 10

Het probleem is nu echter dat 'ik 'user' en 'timestamp' van forummsg wil sorteren op 'timestamp', zodat ik de laatste user krijg. Hoe sorteer ik dat? Ik heb al diverse manieren geprobeerd, onder andere met 'MAX(timestamp) AS timestamp)' en dan 'ORDER BY timestamp'. In dat laatste geval heb ik wel de tijd van het laatste bericht, maar niet de goede user (namelijk de eerste poster)

[ Voor 8% gewijzigd door StephanVierkant op 04-11-2007 16:11 ]

zondag 4 november 2007 13:42

Acties:

bartware

@jabber.org

Begrijp ik goed dat je de timestamp van de laatste message, maar de username van de eerste message in een topic wilt tonen?

Overigens zou ik de tabel forum hernoemen naar topic voor de duidelijkheid.

Heb ik me begrepen?
Cycle Vision 2020: 17-20 juli Sportpark Sloten & Wheelerplanet Spaarnwoude

zondag 4 november 2007 13:46

Acties:

Kalentum

Ik begrijp niet helemaal wat je bedoelt maar het lijkt erop dat je de nieuwste post wilt hebben. Dan kan je sorteren op timestamp en dan aflopend sorteren.

code:

1	ORDER BY forummsg.timestamp DESC

zondag 4 november 2007 13:47

Acties:

curry684

left part of the evil twins

Programming FAQ - SQL - Hoe werkt dat GROUP BY nu eigenlijk?

Begin daar eerst eens mee om je GROUP BY clause te fixen naar voorspelbaar gedrag

Professionele website nodig?

zondag 4 november 2007 14:46

Acties:

StephanVierkant

Topicstarter

bartware schreef op zondag 04 november 2007 @ 13:42:
Begrijp ik goed dat je de timestamp van de laatste message, maar de username van de eerste message in een topic wilt tonen?

Overigens zou ik de tabel forum hernoemen naar topic voor de duidelijkheid.

Nee, ik wil de laatste user en laatste timestamp hebben, maar met de sql-query in de start post krijg ik de eerste user.

rutgerw schreef op zondag 04 november 2007 @ 13:46:
Ik begrijp niet helemaal wat je bedoelt maar het lijkt erop dat je de nieuwste post wilt hebben. Dan kan je sorteren op timestamp en dan aflopend sorteren.
code:
1
ORDER BY forummsg.timestamp DESC

Dat werkt inderdaad voor de timestamp, maar niet voor de user. Ik krijg dan de eerste user.

zondag 4 november 2007 16:13

Acties:

StephanVierkant

Topicstarter

curry684 schreef op zondag 04 november 2007 @ 13:47:
Programming FAQ - SQL - Hoe werkt dat GROUP BY nu eigenlijk?

Begin daar eerst eens mee om je GROUP BY clause te fixen naar voorspelbaar gedrag

Heb het helemaal doorgelezen, maar snap niet hoe ik het zou moeten verbeteren. Ik gebruik overigens PHP i.c.m. MySQL.

zondag 4 november 2007 16:46

Acties:

curry684

left part of the evil twins

Stephan4kant schreef op zondag 04 november 2007 @ 16:13:
[...]

Heb het helemaal doorgelezen, maar snap niet hoe ik het zou moeten verbeteren. Ik gebruik overigens PHP i.c.m. MySQL.

Korte samenvatting: zodra je GROUP BY in een query gebruikt, moeten alle velden in de SELECT list oftewel in de GROUP BY staan oftewel een aggregate functie gebruiken. Dat je MySQL gebruikt had ik al wel door, gezien het feit dat geen enkele andere database deze query zou slikken zonder tig foutmeldingen, je hebt namelijk 4 velden in je select staan zonder aggregate die ook niet in de group by staan, en het enige veld waarop je groupt staat vervolgens niet in je select.

MySQL geeft je nu tot op zekere hoogte random data terug.

[ Voor 4% gewijzigd door curry684 op 04-11-2007 16:47 ]

Professionele website nodig?

zondag 4 november 2007 17:00

Acties:

StephanVierkant

Topicstarter

Helemaal willekeurig is het niet, aangezien ik wel netjes de laatste timestamp terugkrijg, maar de eerste user. Ik zou echt niet weten hoe ik dit zou moeten oplossen.

zondag 4 november 2007 17:27

Acties:

Icelus

Als ik het goed begrijp wil je een zgn. ‘groupwise maximum’.
Zie de post van Csaba Gabor voor een oplossing zonder group by.

[ Voor 7% gewijzigd door Icelus op 05-11-2007 10:11 ]

Developer Accused Of Unreadable Code Refuses To Comment

zondag 4 november 2007 17:39

Acties:

StephanVierkant

Topicstarter

Noem me dom, maar ik begrijp er nog geen reet van.

zondag 4 november 2007 17:52

Acties:

Voutloos

Dan lees je nog wat meer erover. Bijvoorbeeld http://jan.kneschke.de/projects/mysql/groupwise-max welke een groupwise maximum op diverse manieren uitlegt.

{signature}

zondag 4 november 2007 18:55

Acties:

AndriesLouw

Je wilt het id/username van de topic starter hebben, en de datum van de laatste reactie, toch?

Dit probleem ben ik ook al eens tegen gekomen, de enige oplossing bij mij was om het id van de topic starter in de tabel forum_topics te zetten.

(Mocht het je nog interesseren, link naar mijn probleem)

Specificaties | AndriesLouw.nl

zondag 4 november 2007 19:00

Acties:

StephanVierkant

Topicstarter

Nee, ik wil datum én username van de laatste reactie.

zondag 4 november 2007 19:21

Acties:

AndriesLouw

Oke, maar dat is vrijwel dezelfde situatie

Specificaties | AndriesLouw.nl

zondag 4 november 2007 19:24

Acties:

StephanVierkant

Topicstarter

En iets met DISTINCT dan? Is dat misschien een optie? Of met een sub-query?

Ik had verwacht dat hier een vrij simpele oplossing voor zou zijn.

[ Voor 31% gewijzigd door StephanVierkant op 04-11-2007 19:24 ]

zondag 4 november 2007 22:29

Acties:

curry684

left part of the evil twins

Stephan4kant schreef op zondag 04 november 2007 @ 17:00:
Helemaal willekeurig is het niet, aangezien ik wel netjes de laatste timestamp terugkrijg, maar de eerste user. Ik zou echt niet weten hoe ik dit zou moeten oplossen.

Dat is niet 'niet helemaal willekeurig', dat is 'stom geluk'.

Als je GROUP BY vertikt te snappen, niet gebruiken want dan blijf je raden naar waarom je er onzin uit krijgt.

Voor dit soort queries heb je in principe subqueries nodig, of simpelweg zoals alle fora ter wereld van React tot vBulletin doen de data denormaliseren in de bovenliggende tabel (je wil niet weten hoe traag dit soort operaties dynamisch zijn zodra je 1 miljoen berichten hebt, laat staan 1 miljoen topics).

[ Voor 29% gewijzigd door curry684 op 04-11-2007 22:31 ]

Professionele website nodig?

maandag 5 november 2007 09:20

Acties:

EfBe

Stephan4kant schreef op zondag 04 november 2007 @ 19:00:
Nee, ik wil datum én username van de laatste reactie.

Dan nog ben je niet duidelijk: wanneer de laatste 4 posts op het complete forumsystem in topic 'ABC' zijn geweest, wil je dan 4 keer 'ABC' zien, of maar 1 keer?

Creator of: LLBLGen Pro | Camera mods for games
Photography portfolio: https://fransbouma.com

maandag 5 november 2007 16:12

Acties:

StephanVierkant

Topicstarter

EfBe schreef op maandag 05 november 2007 @ 09:20:
[...]

Dan nog ben je niet duidelijk: wanneer de laatste 4 posts op het complete forumsystem in topic 'ABC' zijn geweest, wil je dan 4 keer 'ABC' zien, of maar 1 keer?

Nee, dan wil ik het topic 'ABC' maar één keer laten zien.

Overigens heeft het 'forum' slechts drie gebruikers. Het is ook meer een testcase, in wat andere fora die ik maakte zette ik alles in de database. Dat wil ik hierbij niet doen.

[ Voor 40% gewijzigd door StephanVierkant op 05-11-2007 16:14 ]

dinsdag 6 november 2007 00:47

Acties:

Milt

Volgens mij is dit wat je zoekt:

SQL:

SELECT forummsg.topicid, subtable2.title, subtable2.maxmsgid, forummsg.user, forummsg.bericht, forummsg.timestamp
FROM forummsg INNER JOIN (
    SELECT subtable1.topicid, forum.title, maxmsgid 
    FROM forum INNER JOIN (
        SELECT topicid, max(id) AS maxmsgid
        FROM forummsg
        GROUP BY forummsg.topicid
        ORDER BY maxmsgid DESC
        LIMIT 10
    ) AS subtable1 ON forum.id = subtable1.topicid
) AS subtable2 ON maxmsgid = forummsg.id
ORDER BY timestamp DESC

[ Voor 3% gewijzigd door Milt op 06-11-2007 00:50 . Reden: LIMIT 10 toegevoegd voor laatse 10 topics ]

dinsdag 6 november 2007 08:53

Acties:

Voutloos

Een eenvoudiger query lijkt me mogelijk als TS gewoon even de moeite neemt om de gegeven links te bestuderen. Met direct een hele query voorschotelen als er alleen nog maar 'begrijp er nog geen reet van' is gezegd is ook niet ideaal, imo leer je namelijk meer door het zelf uit te zoeken en dat is echt niet zo lastig.

{signature}

dinsdag 6 november 2007 12:50

Acties:

Milt

@Voutloos: Ik denk dat die al best eenvoudig is zo. Ik denk niet dat het eenvoudiger kan. Als Stephan4kant er interesse in heeft wil ik wel uitleggen hoe de query werkt mocht hij dat niet begrijpen.

Ik denk dat je wel wat optimistich bent om te denken dat iemand door het lezen van wat webpagina's ineens snapt hoe dit probleem op te lossen. De uitleg op de genoemde links zijn nou niet echt super om eerlijk te zijn. Queries waar je GROUP-BY nodig hebt zijn meestal niet echt eenvoudig en zeker niet met MySQL omdat de implementatie van GROUP-BY in MySQL niet correct is (vind ik althans).

dinsdag 6 november 2007 12:54

Acties:

Voutloos

Milt schreef op dinsdag 06 november 2007 @ 12:50:De uitleg op de genoemde links zijn nou niet echt super om eerlijk te zijn

Alles staat er en de basics (group by werking) wordt gewoon prima uitgelegd in (my)sql docs of zelfs de programming faq.

zeker niet met MySQL omdat de implementatie van GROUP-BY in MySQL niet correct is (vind ik althans).

Ja dat is stom, nee, dat maakt het niet ingewikkelder. Overigens is er ook een SQL_MODE optie voor om het stomme gedrag uit te zetten tijdens het devven: 'ONLY_FULL_GROUP_BY' . \o/

{signature}

Pagina: 1

Reageer