[SQL] Posts met aantal reacties in 1 query

[Bosmonster]

Misschien is het al te laat.. maar ik zat mn hoofd even te breken over een query

Stel tabelletje 'posts' met velden ID, poster, datum, tekst, parent (parent is FK naar zelfde tabel).

Nu wil ik een overzicht genereren van de laatste 30 posts (niet zon probleem ), maar ik wil in 1 query direct het aantal reacties (dus waar de parent de huidige ID is) hebben.

Ik kom er eerlijk gezegd niet echt uit.. de query's die ik verzonnen heb zijn het posten niet eens waard

Iemand een duwtje in de goede richting?

[momania]

JOIN op dezelfde tabel en dan een count van een id...

SQL:

select
  topics.poster,
  topics.datum,
  topics.tekst,
  count(reacties.id)
from
  (posts topics
     left join posts reacties on reacties.parent = posts.id)
where
  reacties.parent <> 0

zoiets? alleen dan nog met je eigen voor waarde erin om de laatste 30 op te halen...

Beetje lastige constructie iig als je de topics en de reacties in één tabel hebt..kan je beter opsplitsen.

[ Voor 93% gewijzigd door momania op 25-11-2002 16:44 ]

[Bosmonster]

Dat had ik, maar omdat ik ze dan ergens op moet joinen werd dat ID=parent... default is parent echter 0 (voor hoofd post). En dan krijg ik dus altijd een lege RS terug

Bij nader inzien deed ik het misschien gewoon verkeerd om..

[ Voor 16% gewijzigd door Bosmonster op 25-11-2002 16:41 ]

[momania]

Bosmonster schreef op 25 november 2002 @ 16:40:
Bij nader inzien deed ik het misschien gewoon verkeerd om..

Doet ie nu wel wat je wilt dan?

[Bosmonster]

Hij doet het wel met de volgende query:

code:

SELECT
    topics.ID, topics.naam, count(replies.ID)
FROM
    weblog as topics, weblog as replies
WHERE
    topics.ID = replies.parent
GROUP BY 
    topics.ID
ORDER BY
    topics.datum DESC
LIMIT 30

Maar hij negeert nu alle 'topics' zonder replies, aangezien de join plaatsvindt op basis van topics.ID=replies.parent ..:? En bij de join vervallen dus al de records zonder replies...

Zover was ik dus ook ongeveer al met mn eigen verzinsels... is hier nog wat aan te doen?

[ Voor 20% gewijzigd door Bosmonster op 25-11-2002 17:19 ]

[Verwijderd]

Misschien met een LEFT / RIGHT JOIN in plaats van INNER JOIN?
Ik weet alleen niet zeker wat er dan met die count(replies.ID) gebeurt...
Maar met een beetje geluk wordt dat automatisch 0!

[ Voor 3% gewijzigd door Verwijderd op 25-11-2002 17:26 ]

[robjanssen]

code:

SELECT topics.ID, topics.naam, count(replies.ID)
FROM weblog as topics
LEFT JOIN weblog as replies ON topics.ID = replies.parent
GROUP BY topics.ID
ORDER BY topics.datum DESC
LIMIT 30

Moet je ook niet een GROUP BY doen topics.naam??

[ Voor 15% gewijzigd door robjanssen op 25-11-2002 19:09 ]

[Bosmonster]

Danke danke danke

Dit is de uiteindelijke volledige query geworden..

code:

SELECT
    topics.ID as ID,
    topics.naam as naam,
    UNIX_TIMESTAMP(topics.datum) as unixdatum,
    topics.openbaar as openbaar,
    topics.bericht as bericht,
    count(replies.ID) as replycount
FROM
    weblog as topics
LEFT JOIN
    weblog as replies
    ON
    topics.ID = replies.parent
WHERE
    topics.parent = 0
GROUP BY
    topics.ID
ORDER BY
    topics.datum DESC
LIMIT 30

Had het niet kunnen doen zonder jullie hulp

GROUP BY topics.ID is overigens voldoende.. het gaat er maar om dat de DB weet hoe de count() uit te voeren.

[ Voor 14% gewijzigd door Bosmonster op 25-11-2002 20:07 ]

[Goodielover]

Goede query nu! Gefeliciteerd.

Toch nog een opmerking op je oplossing.
Om bij parent een 0 op te nemen als het een topic-start is gaat problemen opleveren als je de FK echt ook als FK definieert in je DBMS.

Normaliter zet je dan NULL in zo'n veld en definieer je dus ook het veld niet als NOT NULL. Je FK definities gaan dan namelijk ook goet. Bovendien zal er maar een record komen met PK=0, dan gaat je query wel raar doen.