[MySQL] Forum bouwen: sorteren op laatste bericht lukt niet

[Joen]

Ik ben bezig een klein forum te bouwen wat ik vooral voor mij zelf als een soort leermoment bouw.
Voor het forum ben ik al een behoorlijk end op weg als het om de basisfunctionaliteiten gaat.

Het volgende punt waar ik mee bezig ben is net zo iets als Tweakers heeft: Active Topics.
De bedoeling is dat er wordt weergegeven in welke bijvoorbeeld 25 topics voor het laatst een bericht is gepost.
Er zal dus gesorteerd moeten worden op laatste post (op basis van de identifier wilde ik dat doen) en dan moet het bijbehorende topic-id er bij worden gezocht.

Het probleem waar ik tegen aan loop is dat ik wel bijvoorbeeld de laatste 25 berichten kan ophalen, maar dat ik meerdere posts uit een zelfde topic terug krijg terwijl ik eigenlijk per topic alleen het laatste bericht wil hebben.

Ik zal wat illustreren wat ik geprobeerd heb.

Ik heb eerst de hier onder staande eerste query geprobeerd. De WHERE clause moet er voor zorgen dat alleen topics uit die fora worden weergegeven waar de ingelogde gebruiker toegang heeft. De fora waar het om gaat wordt doormiddel van een andere query en ene stukje PHP al eerder uitgezocht.

code:

SELECT p.id, p.topicid, p.subcatid, p.userid, p.laatstewijziging, p.datumtijd, t.id, t.titel, p.bericht
FROM forum_posts AS p
LEFT JOIN forum_topics AS t ON (t.id=p.topicid)
WHERE p.subcatid='1' OR p.subcatid='2' OR p.subcatid='3' OR p.subcatid='4' OR p.subcatid='5' OR p.subcatid='6' OR p.subcatid='7' OR p.subcatid='8'

ORDER BY p.id DESC, p.topicid DESC

Hier onder zie je een plaatje uit SQL Front met de resultaten die ik krijg.
Hierbij zie je onder andere dat ik id 56 krijg met als titel (titel van het topic) "Mededeling en gesloten", maar ik krijg echter meerdere van het topic "Mededeling en gesloten" waar onder id 1.


Probeer ik het met een GROUP BY op t.id of op p.topicid met dus deze query:

code:

SELECT p.id, p.topicid, p.subcatid, p.userid, p.laatstewijziging, p.datumtijd, t.id, t.titel, p.bericht
FROM forum_posts AS p
LEFT JOIN forum_topics AS t ON (t.id=p.topicid)
WHERE p.subcatid='1' OR p.subcatid='2' OR p.subcatid='3' OR p.subcatid='4' OR p.subcatid='5' OR p.subcatid='6' OR p.subcatid='7' OR p.subcatid='8'
GROUP BY p.topicid
ORDER BY p.id DESC, p.topicid DESC

Dan krijg ik het resultata uit de afbeelding hier onder en dan valt me op dat ik niet het nieuwste bericht van het topic "Mededeling en gesloten" krijg te zien met het id 56, maar juist de oudste post in het topic met id 1.


Hoe kan ik het nu voor elkaar krijgen dat ik maar 1 laatste post per topic krijg te zien en de lijst op basis van de laatste post wordt gesorteerd?
Mijn inziens is juist de "GROUP BY p.topicid" het meest logische en zou de "ORDER BY p.id" er voor moeten zorgden dat het laatste post-id 56 ipv 1 als ennigste voor het topic "Mededeling en gesloten" moeten worden weergeven, maar wat ik ook probeer (andere sorteringen, niet groeperen, etc.): ik krijg het niet voor elkaar.

In de online MySQL documentatie en enkele websites kon ik ook niet echt goed relevante informatie over het probleem vinden.

[Serpie]

Een Max gebruiken op p.Id in combinatie met je Group By.

Verder is het ook aan te raden om de overige velden in je Group By op te nemen, de enige velden die daar niet in staan zijn de velden met een aggregate funtie eromheen (zoals Max, Min, Sum etc.).

Zo weet je altijd welke velden gegroepeerd worden, en met welke velden je iets 'speciaals' wilt doen.

[ Voor 95% gewijzigd door Serpie op 28-01-2008 22:32 ]

[Joen]

Met deze query:

code:

SELECT MAX(p.id) AS nwpostid, p.topicid, p.subcatid, p.userid, p.laatstewijziging, p.datumtijd, t.id, t.titel, p.bericht
FROM forum_posts AS p
LEFT JOIN forum_topics AS t ON (t.id=p.topicid)
WHERE p.subcatid='1' OR p.subcatid='2' OR p.subcatid='3' OR p.subcatid='4' OR p.subcatid='5' OR p.subcatid='6' OR p.subcatid='7' OR p.subcatid='8'
GROUP BY p.topicid
ORDER BY nwpostid DESC

Krijg ik nu deze uitkomst:


En dat is nu volgens mij precies wat ik zoek.

Ik begrijp nu dus ik met behulp van MAX() een draai aan de GROUP BY kan geven op welke manier er gegroepeerd moet worden.

Wat bedoel je precies met ook de andere velden in de GROUP BY op te nemen? Alleen de p.topicid is in mijn geval toch voldoende of heb ik dat mis?

[Serpie]

Joen schreef op maandag 28 januari 2008 @ 22:55:
Wat bedoel je precies met ook de andere velden in de GROUP BY op te nemen? Alleen de p.topicid is in mijn geval toch voldoende of heb ik dat mis?

Ja het werkt wel, maar het werkt overzichtelijker en onderhoudbaarder om alle velden zonder aggregate op te nemen in de group by. Je kan dan ook in 1 oogopslag zien wat er gebeurt en problemen die je had voorkomen. Namenlijk ipv dat MySql zelf een waarde uitzoekt, jij dat zelf bepaalt.

zie ook: http://www.mysqlperforman...kes-your-queries-fragile/

[Voutloos]

Order by is nog niet de volgorde binnen de group. Probeer je vervolgens voor te stellen hoe mysql de geselcteerde waarden welke niet in een aggregate function of in de group by staan moet kiezen en je zal begrijpen dat je ipv een nette error heel 'aardig' random data krijgt.

Hoe group by werkt en de 'feature' van mysql is heel veel te vinden, zo ook in de faq hier. En mysqlperformanceblog is sowieso een aardige site om bij te houden als je veel met mysql bezig bent. Op mysqlperformanceblog is ook een keer het idee van een GROUPORDER BY langsgekomen, wat het gedrag van order by icm groups moet zijn zoals jij het verwachtte, wat op zich wel een grappig idee is.