[SQL] Geneste commentaren query

[saillemuin]

Ik probeer een simpele versie van het commentarensysteem van tweakers.net te schrijven, maar geraak er niet uit wat mijn sql statement betreft, hopelijk kunnen jullie me helpen.

De structuur van mijn commentaren-tabel is:
- id (auto indexering)
- parent_id
- boodschap

Wanneer er commentaar op een commentaar geplaatst wordt, dan is de parent_id = de id van het item waar commentaar op wordt gegeven.
Als het een nieuw commentaar is op het nieuwsbericht, zonder 'ouder' dan is de parent_id= de id van het nieuwe bericht

Als test data heb ik volgende records:
- id:1 parent_id: 1 boodschap: '1.commentaar'
- id:2 parent_id: 2 boodschap: '2. commentaar'
- id:3 parent_id: 1 boodschap: '3. commentaar op bericht 1'
- id:4 parent_id: 1 boodschap: '4. commentaar op bericht 1'
- id:5 parent_id: 3 boodschap: '5. commentaar op bericht 3'

De volgorde waarin de commentaren moeten getoond worden is dus:

1. Commentaar (id 1)
   • Commentaar op bericht 1 (id 3)
     • Commentaar op bericht 3 (id 5)
   • Commentaar op bericht 1 (id 4)
2. Commentaar (id 2)

Ik vind nu maar niet hoe ik via een sql statement de records in de correcte volgorde kan opvragen.
Na wat googlen besef ik dat het met een self-join zal zijn, waarschijnlijk een outer join, maar ik kom er niet uit hoe ik deze query kan opstellen. Hopelijk jullie wel !

[ValHallASW]

Dit gaat je niet met één query lukken, omdat je per tabelreference maar één niveau diep kunt ophalen. Wat je wel kunt doen is meerdere queries gebruiken (een per niveau) of een ander datamodel gebruiken: Modified Preorder Tree Traversal

[Y0ur1]

Hij is al vaak voorbij gekomen, maar niet minder relevant: http://www.sitepoint.com/article/hierarchical-data-database/. Deze is wat je zoekt: Crisp's blog: Formatting a multi-level menu using only one query

Wat je in feite wil is een tree opslaan in een database

[dev10]

Je moet gewoon deze query gebruiken:

SQL:

SELECT * FROM comments WHERE nieuws_item_id = '1'

Vervolgens in PHP afvangen of een reactie geneste reacties heeft.

Zie ook: Crisp's blog: Formatting a multi-level menu using only one query

edit:
Crap, Y0ur1 was eerder.

[ Voor 5% gewijzigd door dev10 op 07-06-2009 12:43 ]

[Janoz]

Een dergelijke recursieve structuur is altijd wat lastig te verwerken in SQL. Je zult een paar consessies moeten doen. Pas je je model niet aan dan zul je een heleboel verschillende queries moeten draaien voor het resultaat. Leg je een beperking op de diepte dan kun je voor elk extra diepte niveau een extra join doen en het alsnog in 1 query kunnen doen.

Je zou ook extra info op kunnen slaan in de db. Denk daarbij aan een diepte veld en een 'thread id' zodat je met 1 query simpel alle berichten, behorende bij 1 thread, uit de database kunt halen. Het in de juiste volgorde zetten is vervolgens simpel in de code op te lossen (zolang je er voor zorgt dat allen parent nodes terug komen voor de child nodes).

Tot slot zie ik nog iets wat ik heel vreemd vind. Wat is in godensnaam de gedachte van het opslaan van het eigen ID in de parent ID? Een rootnode heeft helemaal geen parent. In de computerwereld hebben we een prachtige 'constante' die 'niks' aangeeft. Gebruik die dan ook.

[ACM]

Als je een database hebt die hierarchische queries ondersteund ('connect by' en of de 'with' zoals in postgresql), dan kan het met een query zonder verdere modificaties aan je tablelen.
Wij slaan bij elke reactie ook op bij welk bericht (nieuwsitem/review/etc) het hoort en constueren dan de volgorde los. Dat is sowieso lastig in SQL op te lossen in ons geval, aangezien we soms berichten wel of niet (deels) willen tonen aan de hand van criteria die ook weer van het bericht een niveau erboven afhangen.

[ Voor 9% gewijzigd door ACM op 07-06-2009 12:56 ]

[saillemuin]

Mijn excuses voor de repost, ik vind het erg moelijk om het probleem te omschrijven, en dus ook om het op te zoeken. Bedankt voor alle reacties, ik ga er mee aan de slag

[RobertMe]

Als het een nieuw commentaar is op het nieuwsbericht, zonder 'ouder' dan is de parent_id= de id van het nieuwe bericht

En hoe weet je dan of parent_id 1 een reactie op een nieuwsbericht of op een andere reactie was? Of kun jij glazen bollen programmeren? Als het commentaar is op het nieuwsbericht is het parent_id NULL, en elke reactie kun je dan nog een kolom geven met het id van de reactie.

[saillemuin]

RobertMe schreef op zondag 07 juni 2009 @ 15:01:
[...]

En hoe weet je dan of parent_id 1 een reactie op een nieuwsbericht of op een andere reactie was? Of kun jij glazen bollen programmeren? Als het commentaar is op het nieuwsbericht is het parent_id NULL, en elke reactie kun je dan nog een kolom geven met het id van de reactie.

Ik heb ondertussen succesvol de oplossing van Crisp geprogrammeerd, hoofdreacties krijgen nu als parent_id null. Bedankt allen voor de hulp