[SQL] Forumthreads

[Rex]

Hoi,

Ik heb een megaprobleem met een query. Ik heb van alles geprobeerd maar krijg het echt niet werkend.
Ik zal kort maar krachtig vertellen wat ik heb en wat ik wil selecteren:

TABLE: THREAD (afgekort t)
FIELDS: id (int), name (varchar(50))

TABLE: POST (afgekort p)
FIELDS: id (int), threadid (int), userid (int), message (text), added (timestamp)

TABLE: USER (afgekort u)
FIELDS: id (int), name (varchar(50))

De volgende relaties bestaan er in mijn database:
u.id = p.userid
t.id = p.threadid

Ik wil het volgende hebben:
Ik wil een lijst van alle threadnamen, daarnaast wil ik weten hoeveel posts er in elke thread zitten (volgens mij gaat dat met een count()) en ik wil ook de username weten van de persoon die voor het laatst in die thread heeft gepost (de datum van de laatste post wil ik ook).

Dus uiteindelijk krijg ik zoiets als:
Results:
Name: Thread ABC
Username: ReflexWolf
Timestamp: 12/12/2012

Als enige eis heb ik dat het allemaal in 1 (!) query moet. (Subqueries zijn wel mogelijk!)
Ik gebruik MySQL 5.0. Iemand die me kan helpen? Thanks in advance.

- ReflexWolf

[Creepy]

En welke queries heb je al geprobreerd? Wat lukte daar niet nee? Zie ook P&W FAQ - De "quickstart"

[Verwijderd]

ReflexWolf schreef op zondag 11 december 2005 @ 19:18:
TABLE: THREAD (afgekort t)
FIELDS: id (int), name (varchar(50))

TABLE: POST (afgekort p)
FIELDS: id (int), threadid (int), userid (int), message (text), added (timestamp)

TABLE: USER (afgekort u)
FIELDS: id (int), name (varchar(50))

De volgende relaties bestaan er in mijn database:
u.id = p.userid
t.id = p.threadid

Ik wil het volgende hebben:
Ik wil een lijst van alle threadnamen, daarnaast wil ik weten hoeveel posts er in elke thread zitten (volgens mij gaat dat met een count()) en ik wil ook de username weten van de persoon die voor het laatst in die thread heeft gepost (de datum van de laatste post wil ik ook).

Dus uiteindelijk krijg ik zoiets als:
Results:
Name: Thread ABC
Username: ReflexWolf
Timestamp: 12/12/2012

SQL:

select threadid, max(col_timestamp) as laatste_wijziging
from post
group by threadid

hier heb je het halve werk

en dan met wat left joins de threadname en username erbij doen ofzo

[Rex]

hmmm me stomme query got lost in action..
maar het kwam hierop neer:

SQL:

SELECT t.id, t.name, u.name, t.added, count(*) AS replies
FROM thread AS t LEFT OUTER JOIN post AS p ON (t.id = p.threadid)
LEFT OUTER JOIN user AS u ON (p.userid = u.id)
GROUP BY threadid
ORDER BY added DESC

Ik kreeg bijv. niet de laatste post datum/naam, maar juist de eerste van de topic, want ORDER BY wordt pas later uitgevoerd dan GROUP BY.... dus wanneer het de GROUP BY runt... dan pakt ie de eerste post...

Ik had ook al geprobeerd om dit toe te voegen:

SQL:

WHERE added = (SELECT max(added) FROM post)

maar dat werkt ook niet. Ik denk wel eigenlijk dat ik met de subquery in de goede pad zit, maar weet nog niet hoe ik het moet oplossen.

[ Voor 47% gewijzigd door Rex op 11-12-2005 19:55 ]

[whoami]

Je zal dat nooit met één query kunnen bereiken.
Het ophalen van alle threads, aantal posts per thread en datum van laatste reactie, dat moet wel in één query kunnen. Die username erbij halen, dat zal je apart moeten doen.

Waarom wil je dat trouwens in één query hebben ?

[Rex]

1 query om het allemaal efficienter te houden..... anders zou ik voor elke naam die ik moet ophalen 1 query moeten uitvoeren. ik kan dan uitkomen op 30 queries.
als ik alle namen uit de tabel haal en alle topics via een andere query.... dan kan ik ze wel combineren via php, maar ik heb nu 60 000 users... en om die elke keer uit de tabel te halen is ook niet echt efficient.
Ik geloof best dat je alles in 1 query kunt duwen (misschien gebruikmakend van subqueries?).

Lastige vraag, I know, maar als het makkelijk was geweest dan had ik het zelf ook gekunt.

[BasieP]

als je het efficienter wilt houden zal je wat redundantie in moetne bouwen, dan kan je namelijk makkelijker data opvragen zonder subquery's of meerdere query's

trouwens, ALS je dit (en ik heb er niet zo heel erg diep naar gekeken dus weet zo 123 niet of het kan) zou lukken in 1 query zou deze query zoveel nullvelden hebben en dermate sloom zijn, dat je beter 30 query's kan doen

[ Voor 44% gewijzigd door BasieP op 11-12-2005 21:33 ]

[Rex]

Eh.... volgens mij zul je helemaal geen nullvelden hebben.
Alle threads hebben op z'n minst 1 post die op z'n minst 1 username hebben.
---
Maar wat bedoel je met redundantie inbouwen? voorbeeld? tip?
---

[BasieP]

ReflexWolf schreef op zondag 11 december 2005 @ 21:47:
Eh.... volgens mij zul je helemaal geen nullvelden hebben.
Alle threads hebben op z'n minst 1 post die op z'n minst 1 username hebben.
---
Maar wat bedoel je met redundantie inbouwen? voorbeeld? tip?
---

maar als jij alle posts en de daarbij behoorende usernames wil, is dus de kolom met de forumnaam heel erg vaak dubbel

trouwens is het zo dat wanneer je je DB lokaal heb draaien, er vaak meer tijd zit in het oversturen van data dan in de query zelf (bij kleinere en goed geindexte DBs iig)

redundantie inbouwen kan bijv. door velden in andere tabellen over te nemen dan waar ze horen.

dus in jouw geval kan je bijv de u.name in de POST tabel zetten als p.uname ofzo, dit is in dit geval een mogelijkheid, maar waarschijnlijk wil je meer info behalve de naam van de user tabel, dus dan is het weer niet zo handig

wat wel kan is bijv de t.name in de post tabel
maar ook dat zou ik in jouw geval niet doen.

wat wel vaak gebeurd is bijv het aantal topics in een forum in de forum tabel zetten, zodat je niet alle topics hoeft te gaan tellen, maar dat je simpel de informatie al heb, en deze ophoogt als je een topic aanmaakt (wat je veel minder vaak doet)

[ Voor 36% gewijzigd door BasieP op 11-12-2005 21:52 ]

[justmental]

SQL:

SELECT t.id, t.name, u.name, t.added, p.added, u.id, count(*) AS replies
FROM thread AS t LEFT JOIN post AS p ON (t.id = p.threadid) 
LEFT JOIN user AS u ON (p.userid = u.id)
LEFT JOIN post AS p3 ON (t.id = p3.threadid) 
WHERE p.added = (SELECT max(p2.added) FROM post p2 WHERE p2.threadid = t.id)
GROUP BY t.id

Ik ben niet zo thuis in die ansi notatie, maar zoiets kun je aan denken.

[jvdmeer]

Ik weet niet wat MySQL 5 ermee doet, maar onder MS-SQL werkt de volgende query:

Hierbij geldt de aanname dat het laatste bericht ook het hoogste id heeft.

SQL:

SELECT t.id AS threadid, t.name, p1.aantal, p2.id AS lastuderid, u.name AS lastusername, p2.added AS lastmsgadded 
  FROM threads t
  JOIN
    (SELECT threadid, count(id) AS aantal, max(id) AS laatste 
      FROM posts 
      GROUP BY threadid
    ) p1 ON t.id=p1.threadid
  JOIN posts p2 ON p2.id=p1.laatste
  JOIN users u ON u.id=p2.userid

[Rex]

Het werkt (de code van justmental) (Ik heb het wel een klein beetje moeten aanpassen, om het helemaal werkend in mijn originele forums te krijgen.) Maar het klopt helemaal. Trouwens als iemand de voorbeeld hierboven gebruikt, let er dan op dat je 2x id selecteert. Dus als je in PHP 'id' gebruikt, dan pakt ie automatisch u.id en NIET t.id.... je kan dat oplossen door SELECT t.id te vervangen door SELECT t.id AS threadid

PS: Ik wist wel dat het kon!
PS2: Redundantie was wel een goed idee geweest voor een kleine forum. Maar als ik 40k posts heb dan word je database zo onnodig groot. Heb liever weinig redundantie en moeilijke queries.

[ Voor 27% gewijzigd door Rex op 11-12-2005 22:33 ]

[Verwijderd]

ReflexWolf schreef op zondag 11 december 2005 @ 22:30:
Trouwens als iemand de voorbeeld hierboven gebruikt, let er dan op dat je 2x id selecteert. Dus als je in PHP 'id' gebruikt, dan pakt ie automatisch u.id en NIET t.id.... je kan dat oplossen door SELECT t.id te vervangen door SELECT t.id AS threadid

Heeft niets met PHP te maken. Lijkt me meer een feature van MySQL. Een echte RDBMS zal hier een mooie error geven i.v.m. ambiguiteit.

ReflexWolf schreef op zondag 11 december 2005 @ 22:30:
Redundantie was wel een goed idee geweest voor een kleine forum. Maar als ik 40k posts heb dan word je database zo onnodig groot. Heb liever weinig redundantie en moeilijke queries.

Volgende stap lijkt me dan... performance problemen...

[Dennis]

ReflexWolf schreef op zondag 11 december 2005 @ 22:30:
Maar als ik 40k posts heb dan word je database zo onnodig groot. Heb liever weinig redundantie en moeilijke queries.

Een database is bedoeld om groot te worden. Anders kan je het net zo goed in Excel doen. Heel veel rijen in een database is niet erg, het moet daarentegen natuurlijk ook weer niet een rotzooi met 30% ongebruikte rijen worden.

[BasieP]

Dennis schreef op zondag 11 december 2005 @ 22:58:
[...]

Een database is bedoeld om groot te worden. Anders kan je het net zo goed in Excel doen. Heel veel rijen in een database is niet erg, het moet daarentegen natuurlijk ook weer niet een rotzooi met 30% ongebruikte rijen worden.

idd, een grote DB heeft JUIST redundantie, denk je dat op got bijv. geen redundante data opgeslagen word? mooi wel hoor, dat is juist het idee van een DB

[Rex]

beetje offtopic, maar ik heb nu al zo'n 225 mb in mijn database (forums niet meegerekend)... dus wat ik probeer is om stap voor stap de database om te bouwen om wat efficienter te werken en minder redundante onderdelen te hebben.
Daarnaast doe ik door middel van CRONs de rijen verwijderen die niet meer nodig zijn. Dus daar heb ik al rekening mee gehouden.
Ik begrijp dat een database bedoeld is om groot te worden, maar met een site/db die bijna 1 GB aan data heeft, moet je toch zoeken naar alternatieven.
Ik denk ook dat tweakers dat ook doet/gedaan heeft.

EDIT: Ik vraag me nu wel sterk af wat GoT doet..... redundatie of niet
EDIT2: Zonder te bashen lijkt me dit wel een interessante discussie! =) Redundantie lijkt me toch geen goed idee voor grotere sites...

[ Voor 18% gewijzigd door Rex op 12-12-2005 00:00 ]

[Verwijderd]

ReflexWolf schreef op zondag 11 december 2005 @ 23:43:
EDIT: Ik vraag me nu wel sterk af wat GoT doet..... redundatie of niet

Redundantie idd. Moet je gebruiken als je een afweging moet maken t.b.v. performance.

ReflexWolf schreef op zondag 11 december 2005 @ 23:43:
EDIT2: Zonder te bashen lijkt me dit wel een interessante discussie! =) Redundantie lijkt me toch geen goed idee voor grotere sites...

Denk je bijvoorbeeld dat de search-functie in GOT realtime is?

[ Voor 9% gewijzigd door Verwijderd op 12-12-2005 00:05 ]

[whoami]

De query die je nu gebruikt, kan dan wel werken in MySQL, maar correct is hij niet. Je gebruikt de GROUP BY verkeerd.

Reduntantie inbouwen is een manier om het snel en efficient te laten werken. Op GoT wordt dit ook gedaan.