[SQL] Joins van tabellen en Count(*)

[DeverauX]

Heb de eerste stappen gezet in het ontwikkelen van een forum. Ik maak hiervoor gebruik van twee tabellen, een voor de postings en een andere voor de replies.
(forum en forum_replies)

Het volgende SQL statement gebruik ik om een overzicht te genereren voor alle topics in een bepaalde categorie:

code:

sqlString = "SELECT ID, DATE_FORMAT(datum, '%e-%m-%Y %H:%i') as datum, 
member, memberID, title, open FROM forum WHERE actief='True' And forumID=" & 
forumID & " ORDER BY open, datum DESC"

Het werkt allemaal zoals het moet, alleen ik wilde ook aangeven hoeveel replies er zijn op ieder topic, en daar ben ik nog niet helemaal uitgekomen.

In eerste instantie dacht ik gewoon een join te maken van de twee tabellen maar dit levert niet het gewenste resultaat, hij geeft dan namelijk alle topics van alle categorieën ipv van de categorie die in de WHERE clausule is opgenomen.
Onderstaande code heb ik hiervoor gebruikt:

code:

sqlString = "SELECT Count (forum_replies.ID) AS Count, forum.ID, DATE_FORMAT
(forum.datum, '%e-%m-%Y %H:%i') as datum, forum.member, forum.memberID, 
forum.title, forum.open FROM forum, forum_replies WHERE 
forum.forumID=forum_replies.forumID And forum.actief='True' And forum.forumID=" 
& forumID & " ORDER BY open, datum DESC"

Ongetwijfeld doe ik iets fout, de vraag is nu alleen nog wat

Iemand een idee?

[Verwijderd]

Je hebt hier GROUP BY voor nodig (zie http://www.w3schools.com/sql/sql_groupby.asp). Groepeer op forum.ID, expirimenteer een beetje, en dan kom je er wel uit.

Succes

[Freak_NL]

Lijkt te kloppen zo, maar het is voor een buitenstaander erg lastig om je SQL query zo te lezen. Misschien kun je de queries wat netter posten? (en in je eigen code ook, geeft meer overzicht)

Dus zoiets:

code:

SELECT select gebeuren
FROM tabellen
WHERE 
  voorwaarde1 AND
  voorwaarde2
ORDER BY

Schept iets meer duidelijkheid.

edit:
MrX glipt er even tussen en heeft waarschijnlijk gelijk
/me gooit GROUP BY en ORDER BY nog wel eens door elkaar

[ Voor 17% gewijzigd door Freak_NL op 28-12-2002 23:26 ]

[Verwijderd]

Even simpel gedacht: Je wilt dus PER forum tellen hoeveel topics dit forum bevat. Dit moet dus zeker in de query aangegeven worden. Dus zoals mrX al zei: group by.
Freestyle (tabelnamen heb ik wat overzichtelijker gemaakt met aliasses):

code:

SELECT count(r.id), t.ip 
FROM topics t, replies r
WHERE 
  r.topicid = t.id AND
  // andere voorwaardes
GROUP BY t.id
ORDER BY 
  t.open, t.datum DESC

Met een beetje puzzelen/ denkwerk moet je een eind komen. Suc6

[ Voor 1% gewijzigd door Verwijderd op 28-12-2002 23:50 . Reden: tiepvout ]

[DeverauX]

GROUP By heb ik al geprobeerd maar wilde niet. ( zal het weer proberen want dan moet ik wel iets fout hebben gedaan )
Met group by kan je toch alleen aangeven iets in de trend van HAVING forumID >2
en niet actief='True' en open='True' ?

Ik wilt rouwens niet het aantal postings per forum, wat dat doe ik inderdaad met group by. Maar ik wil het aantal postings per topic.
Over de code; zal voortaan een iets meer leesbare versie posten

[ Voor 31% gewijzigd door DeverauX op 29-12-2002 00:06 ]

[Verwijderd]

Trust me, je moet GROUP BY gebruiken, en HAVING heeft er niets mee te maken. Lees de uitleg van de link die ik je gaf nog eens, of anders mijn uit leg in dit topic: [rml]MrX in "[ mysql] snap resultaat van group by en m"[/rml]

Overigens vergiste ik me in de kolom waarop je moet groeperen .... ik bedoelde forum_replies.ID, als ik je datastructuur goed begrijp ...

[DeverauX]

Ik ben nu in ieder geval zo ver dat ik er iets uit krijg. De getallen kloppen nog niet, maar dat wordt nog wel even stoeien.
Volgens mij moet ik groeperen op forum.ID

Het getal van de Count(*) is echter gelijk aan het totaal aantal reacties van alle topics ipv die van elke afzonderlijke topic.

Dus:

Topic| replies
-------------------------
titel | 7 |
titel | 7 |
-------------------------

[ Voor 9% gewijzigd door DeverauX op 29-12-2002 00:52 ]

[Verwijderd]

Een tip misschien: Je kan best vanuit efficiëntie redenen overwegen om een extra veld forum_totalPosts bij te houden, en dat getalletje bij een nieuw post telkens met 1 te verhogen. Je forum-select-query gaat dan heel wat efficiënter/sneller zijn, in ruil voor 'wat' redundantie.

[drm]

topics left join forum op forumID,
topicID counten
group by forum id

[Verwijderd]

drm schreef op 29 december 2002 @ 00:51:
topics left join forum op forumID,
topicID counten
group by forum id

Vraag: waarom een left join, en geen inner join? Je kan toch onmogelijk een topic hebben dat behoort tot een niet bestaand forum? Maw: een topic behoort toch altijd tot een forum?

[ Voor 8% gewijzigd door Verwijderd op 29-12-2002 00:55 ]

[drm]

Nee, en dat mag ook niet, dus is het qua semantiek beter om een LEFT JOIN te gebruiken (imho)

[DeverauX]

Heb nu de volgende code: het groeperen gaat goed, alleen de Count() is nog steeds die van het totaal aantal replies ipv de replies per topic

SELECT Count(forum_replies.parent_ID) AS Count, forum.ID, forum.member, forum.memberID, forum.title, forum.open

FROM forum, forum_replies

WHERE forum.forumID=forum_replies.forumID And forum.forumID=" & forumID & " And forum.actief='True'

GROUP BY forum.ID
ORDER BY forum.open, forum.datum DESC

[ Voor 6% gewijzigd door DeverauX op 29-12-2002 00:59 ]

[Verwijderd]

drm schreef op 29 December 2002 @ 00:56:
Nee, en dat mag ook niet, dus is het qua semantiek beter om een LEFT JOIN te gebruiken (imho)

Left join zou hier toch 'ook' topics gaan neerhalen waarvan forum_id niet bestaat in de forum table. Omdat dit onmogelijk is, is die left joint toch 'nutteloos' en gebruiken we toch de strengere inner join (die wel voldoet aan de relatie 'topic is onderdeel van forum'). Of vergis ik mij nu zo?

[DeverauX]

Verwijderd schreef op 29 December 2002 @ 01:02:
[...]


Left join zou hier toch 'ook' topics gaan neerhalen waarvan forum_id niet bestaat in de forum table. Omdat dit onmogelijk is, is die left joint toch 'nutteloos' en gebruiken we toch de strengere inner join (die wel voldoet aan de relatie 'topic is onderdeel van forum'). Of vergis ik mij nu zo?

Was ook mijn redenatie om gewoon een inner-join te gebruiken.

Baal wel een beetje dat MySQL geen gecorreleerde subqueries ondersteund

[drm]

hmja, you've got a point there ..

devraux counten op topic id, niet op forum id ... Je wilt toch het aantal topics (of forum_replies ) weten, niet het aantal parents

[DeverauX]

ik wil het aantal replies op een topic weten vandaar dat ik de parent_ID tel, maar het maakt trouwens ook niet uit waar ik op tel ik krijg toch altijd het totaal aantal replies terug.

[DeverauX]

Eindelijk ben ik eruit! Ben gisteravond een uur bezig geweest maar kreeg op het eind wel het correcte getal van de aantal replies( maar kreeg niet de topics waar 0 replies op waren)

En nu, zit er net 5 minuten achter en voila, I got it!

SELECT Count(forum_replies.parent_ID) AS Count, forum.ID, DATE_FORMAT(forum.datum, '%e-%m-%Y %H:%i') as datum, forum.member, forum.memberID, forum.title, forum.open

FROM forum LEFT JOIN forum_replies ON forum.ID=forum_replies.parent_ID

WHERE forum.forumID=" & forumID & " And forum.actief='True' GROUP BY forum.ID

ORDER BY forum.open, forum.datum DESC"

Thnx voor de hulp allemaal.

[DeverauX]

Ik denk dat ik toch iets te vroeg gejuicht heb, er is namelijk nog een klein probleempje met de ORDER BY. Ik weet wel wat de oorzaak is maar weet niet hoe ik het kan oplossen.

code:

SELECT Max(DATE_FORMAT(forum_replies.datum, '%e-%m-%Y %H:%i')) AS 
datum_reply, Count(forum_replies.parent_ID) AS Count, forum.ID, 
DATE_FORMAT(forum.datum, '%e-%m-%Y %H:%i') as datum, forum.member,
forum.memberID, forum.title, forum.open 

FROM forum LEFT JOIN forum_replies ON forum.ID=forum_replies.parent_ID  

WHERE  forum.forumID=" & forumID & " And forum.actief='True' 

GROUP BY forum.ID 

ORDER BY forum.open, forum.datum DESC, datum_reply DESC"

Het probleem is namelijk dat het sorteren op tijd niet goed gaat, in eerste instantie wordt er namelijk gesorteerd op de datum van de posting en dan pas op de datum van de replies.
En aangezien ik een regel heb met Laatste Post krijg je dus qua datum een gesorteerde lijst waarbij hij alle eerste (nieuwe) topics boven (nieuwe) replies plaatst!
Dit terwijl de datum van deze reply nieuwer is dan die van de posting.
Ben gisteren een halve dag bezig geweest met puzzelen maar dit mocht niet baten.

[drm]

Gewoon de volgorde van de order by-clause aanpassen:

code:

ORDER BY
   forum.open,
   datum_reply DESC,
   forum.datum DESC

[DeverauX]

dan krijg ik een probleem de andere kant op; dan worden namelijk de nieuw geplaatste topics onder de geplaatste replies geplaatst.
Dit terwijl deze eerder geplaatst zijn! (mits het aantal reacties 0 is echter)

[drm]

ah zo...
Maar dan is er volgens mij iets mis met je database ontwerp.... Bij jou kan een topic dus 0 replies hebben? Volgens mij moet het eerste "reply" op een topic gewoon de openingspost zijn. Dan heb je hiermee geen probleem meer.

[DeverauX]

ik heb de topics en de replies in twee verschillende tabellen staan, met in de tabel met replies een parent_ID welke verwijst naar de topic. Is het dan verstandiger beiden gewoon in een tabel te plaatsen?
zou in ieder geval qua SQL eenvoudiger zijn

[drm]

Nou, het is het meest logisch (en uiteindelijk eenvoudig), om in de topic-tabel enkel topictitel/datum/etc... te zetten, en de openingspost de 1^e "reply" op het topic te laten zijn. Dus je houdt wel 2 verschillende tabellen, maar de openingspost staat dan niet meer in de tabel 'topics' maar in de tabel 'replies'.

[DeverauX]

Kleine aanpassing dan dus. (gelukkig )
thnx

[DeverauX]

Heb de aanpassing gemaakt en het probleem met de datum is idd opgelost!
Blijft er nog een klein iets waar ik problemen mee heb, het tellen van het aantal topics en postings per forum:

SELECT COUNT(forum.ID) AS count_postings, Count(forum_replies.ID) as
count_replies, forum.forumID AS forumID

FROM forum, forum_replies WHERE forum.forumID=forum_replies.forumID

GROUP BY forum.forumID

Het gaat volgens mij fout in de WHERE clause, want ik krijg namelijk als telling een vermenigvuldiging van het aantal topics met het aantal postings.
Dus zijn er bijvoorbeeld 3 topics en 9 reacties op deze topics, dan krijg ik als Count het getal 27

[drm]

waarom count je nou de forums en topics tegelijk

[DeverauX]

omdat ik in een querie zowel het aantal topics als het aantal replies wil weten.
Zodat je net zoals hier bij GOT kan zien hoeveel topics er bijvoorbeeld in /14 zitten en ook hoeveel replies.
Zou ik dit anders in twee verschillende queries moeten doen?

[ Voor 29% gewijzigd door DeverauX op 31-12-2002 13:25 ]

[DeverauX]

Denk niet dat het gaat lukken in één query zowel het aantal topics als het aantal replies te krijgen. (omdat ze in twee verschillende tabellen zitten ?).

Ik heb het nu opgelost met 2 queries, vervolgens de resultaten in een array geplaatst en zo dan maar opvragen.

Mocht iemand een beter alternatief hebben dan hoor ik het graag.
thnx anyway