[MySQL] comment/reply query optimaliseren

[RSD]

Ik ben bezig met een comment reply systeem waarbij men op de comments 1 level diep een reply kan geven, dus geen oneindig aantal replies.

Mijn database ziet er alsvolgt uit:

comments
- id
- imageid
- comment
- date

replies
- id
- commentid
- imageid
- reply
- date

Nu wil ik in 1 query alle comments + eventuele replies op een comment van een image ophalen. Niet elke comment hoeft een reply te hebben.

Zelf had ik de volgende in gedachte:

SELECT c.*,r.* FROM comments AS c,replies AS r WHERE c.imageid='$imageid' OR (r.imageid='$imageid' AND r.commentid=c.id)

Nu haalt hij wel de comments en replies op, maar als ik de query in phpMyAdmin uitvoer, krijg ik een hele brede tabel te zien met veel dubbele informatie. Elke rij heeft zeg maar een comment gedeelte en een reply gedeelte.

Ik vraag me n af of het niet makkelijker/beter kan. Bijvoorbeeld dat MySql voor elke comment/reply een rij aanmaakt, ipv dat mysql een rij teruggeeft die zowel een comment als een reply bevat.

[Gerco]

Ik denk dat je UNION wilt gebruiken.

SQL:

(SELECT * FROM comments c WHERE c.imageid = $imageid)
UNION
(SELECT * FROM replies r WHERE r.imageid = $imageid)

Overigens is het vziw niet echt handig om deze structuur te hanteren, je hebt nu 1 soort data (een reply op een image) in twee tabellen gesplitst. Zou het niet beter zijn om gewoon een "comments" tabel te maken met een veldje "parentid"? Dan krijg je er gratis oneindige diepte bij (je kunt kiezen of je dat wilt gebruiken of niet) en je queries worden een stuk eenvoudiger.

[ Voor 69% gewijzigd door Gerco op 16-08-2006 13:14 ]

[NMe]

Ik denk dat je datamodel niet klopt. Je kan beter een soort van "container" maken, iets als een topic op een forum zegmaar, en die hebben gewoon posts. Vergelijkbaar met jouw geval: een topic is niet de eerste post, het bevat hem alleen.

[RSD]

Dat begrijp ik niet helemaal NME.. ik kan beter alle comments/replies in 1 tabel drukken en dan aangeven of het een post of een reply is?

[NMe]

Beiden zijn simpelweg gewoon posts als ik je goed begrijp. Het enige verschil is dat een reply een parent heeft, en een comment niet. Dan is het toch logisch om dit in dezelfde tabel op te slaan?

[moozzuzz]

Ik denk dat je comments en replies in één table kan stoppen, daar die essentieel dezelfde datastructuur hebben.

comments
- commentid
- parent
- image
- body
- date

Een reply op comment 5 heeft dan parent=5. Comment 5 heeft dan of zichzelf als parent of parent=null.

[Gerco]

Nee, je hoeft helemaal geen onderscheid te maken tussen een post en een reply. Die twee zijn namelijk hetzelfde, het enige verschil is dat een "post" zoals je het noemt geen parentpost heeft en een reply wel.

Ik zou het (waarschijnlijk) zo doen:

images:
- imageid
- url

comments:
- commentid
- imageid
- parentid (referentie naar eventuele parent comment)

In je software kun je dan bepalen of je wilt toestaan dat een reply nog een reply heeft of niet. Voor je structuur maakt dat niet uit.

[RSD]

En dan parentid bij een comment als null geven en bij een reply desbetreffende parent commentid.

[NMe]

moozzuzz schreef op woensdag 16 augustus 2006 @ 13:20:
Een reply op comment 5 heeft dan parent=5. Comment 5 heeft dan of zichzelf als parent of parent=null.

Zichzelf is lastiger, omdat je het id niet weet op het moment van insert. Ik zou dan null gebruiken.

[BalusC]

Of gewoon 0, aangezien datatypen in regel niet null kunnen zijn.

[RSD]

Maar hoe haal ik dit dan fatsoenlijk uit de database. Met GROUP BY imageid,parentid gaat het niet lukken.. Ik wil ze er dan alsvogt uithallen

- comment 1
- reply op comment 1
- nog een reply op comment 1
- comment 2
- comment 3
- reply op comment 3
etc..

[Dido]

-NMe- schreef op woensdag 16 augustus 2006 @ 13:22:
Zichzelf is lastiger, omdat je het id niet weet op het moment van insert. Ik zou dan null gebruiken.

Weet je wel op het moment van insert, lijkt me? Anders kom je er ook nooit meer achter.

RSD schreef op woensdag 16 augustus 2006 @ 13:31:
Maar hoe haal ik dit dan fatsoenlijk uit de database. Met GROUP BY imageid,parentid gaat het niet lukken.. Ik wil ze er dan alsvogt uithallen

- comment 1
- reply op comment 1
- nog een reply op comment 1
- comment 2
- comment 3
- reply op comment 3
etc..

Als ej als parentID voor het comment zijn eigen nummer invult is het dus eenvoudig:

ORDER BY parentID, replyID

[RSD]

Dat zal dus een GROUP BY worden, maar als er dan een comment wrdt geinsert, moet er erna weer een update gedaan worden...toch?

[moozzuzz]

RSD schreef op woensdag 16 augustus 2006 @ 13:43:
Dat zal dus een GROUP BY worden, maar als er dan een comment wrdt geinsert, moet er erna weer een update gedaan worden...toch?

Is het niet verstandig eerst es wat meer naar de SQL-manual en tutors op inet te kijken? Weet je wat GROUP BY doet? Dido geeft je overigens de juiste tip volgens mij.

[Dido]

RSD schreef op woensdag 16 augustus 2006 @ 13:43:
Dat zal dus een GROUP BY worden, maar als er dan een comment wrdt geinsert, moet er erna weer een update gedaan worden...toch?

Ik begrijp uit deze vragen dat je totaal niet inziet wat voor oplossing er wordt aangedragen.

Een group by is hier onzinnig, vergeet dat je die woorden ooit gezien hebt tot je wat meer inzicht hebt in SQL

En nee, er zijn geen updates nodig tenzij iemand een comment/reply (hell, dat zijn dezelfde dingen!) aanpast.

[RSD]

Als ik toch het volgende doe om een comment te inserten:

INSERT INTO comments (id,imageid,parentid,comment,date) VALUES ('','$imageid','$XXXXXXX','$comment',NOW()) en id is een auto increment veld. Hoe kan ik dan $XXXXXXX invullen als je id nog niet weet en deze toch hetzelfde moet zijn als $XXXXXXX.

Daarnaast kom je bij order by op id's toch in de problemen met sorteren op datum? Als bijvoorbeeld een hoop spam is toegevoegd en je delete dit, zullen deze id's later toch ook wel weer eens ingevuld worden door auto icrement. De sortering klopt dan niet meer. Of zit ik er nu flink naast.

[ Voor 45% gewijzigd door RSD op 16-08-2006 14:01 ]

[NMe]

Dido schreef op woensdag 16 augustus 2006 @ 13:34:
[...]

Weet je wel op het moment van insert, lijkt me? Anders kom je er ook nooit meer achter.

Uitgaande van een autoincrement veld weet je dat pas na het inserten, dus heb je twee queries nodig voor de invoer van een enkel record.

[Reinier]

Nee, een auto increment wordt juist niet opnieuw uitgedeeld, dus je komt niet in de problemen met je sortering.

[Soultaker]

BalusC schreef op woensdag 16 augustus 2006 @ 13:25:
Of gewoon 0, aangezien datatypen in regel niet null kunnen zijn.

Nee, géén 0, maar NULL! Waarden kunnen in relationele databases juist wél NULL zijn en 0 is in principe gewoon een geldige identifier. 0 gebruiken als NULL is theorethisch gewoon fout, en er is praktisch geen enkele reden om het te doen. Laat dat dus.

RSD schreef op woensdag 16 augustus 2006 @ 13:31:
Maar hoe haal ik dit dan fatsoenlijk uit de database. Met GROUP BY imageid,parentid gaat het niet lukken.. Ik wil ze er dan alsvogt uithallen

- comment 1
- reply op comment 1
- nog een reply op comment 1
- comment 2
- comment 3
- reply op comment 3
etc..

Dat is een heel ander onderwerp, waar al 'tig topics over zijn. Met twee nivo's is het nog simpel; met een onbeperkt aantal nivo's moet je extra informatie in de database stoppen, of (makkelijker, en met een relatief klein aantal comments - zeg, onder de 1000 - goed te doen) in je applicatiecode (PHP?) de boel herschikken.

[Dido]

RSD schreef op woensdag 16 augustus 2006 @ 13:59:
Daarnaast kom je bij order by op id's toch in de problemen met sorteren op datum? Als bijvoorbeeld een hoop spam is toegevoegd en je delete dit, zullen deze id's later toch ook wel weer eens ingevuld worden door auto icrement. De sortering klopt dan niet meer. Of zit ik er nu flink naast.

Ik mag hopen dat autoincrement geen gevallen gaten gaat invullen in MySQL. Als dat zo is heb je gelijk, maar hoewel ik van MySQL veel verwacht is dat wel heel erg grof.

-NMe- schreef op woensdag 16 augustus 2006 @ 14:04:
Uitgaande van een autoincrement veld weet je dat pas na het inserten, dus heb je twee queries nodig voor de invoer van een enkel record.

Ik zag inderdaad iets over het hoofd
Mgoed, hoe erg is het voor je performance om bij dergelijke replies een extra update te doen? Ik heb niet het vrermoeden dat het om duizenden nieuwe replies per minuut gaat.

Soultaker schreef op woensdag 16 augustus 2006 @ 14:12:
Nee, géén 0, maar NULL! Waarden kunnen in relationele databases juist wél NULL zijn en 0 is in principe gewoon een geldige identifier. 0 gebruiken als NULL is theorethisch gewoon fout, en er is praktisch geen enkele reden om het te doen. Laat dat dus.

offtopic:
En ik zit tegen een DBMS aan t elullen dat geen NULL kent
In een taal die wel *NULL kent maar die gelijkstelt aan 0 of spaties afhankelijk van het datatype.
En het is niet eens iets nieuws als MySQL.

[ Voor 22% gewijzigd door Dido op 16-08-2006 14:39 ]

[NMe]

Dido schreef op woensdag 16 augustus 2006 @ 14:36:
[...]


Ik zag inderdaad iets over het hoofd
Mgoed, hoe erg is het voor je performance om bij dergelijke replies een extra update te doen? Ik heb niet het vrermoeden dat het om duizenden nieuwe replies per minuut gaat.

Waarom zou je, als je NULL kan gebruiken? MySQL kan daar wel mee overweg hoor.

[Dido]

-NMe- schreef op woensdag 16 augustus 2006 @ 14:40:
Waarom zou je, als je NULL kan gebruiken? MySQL kan daar wel mee overweg hoor.

Omdat dan je ORDER BY parentID, commentID niet meer werkt

[ACM]

Dido schreef op woensdag 16 augustus 2006 @ 14:36:
Ik mag hopen dat autoincrement geen gevallen gaten gaat invullen in MySQL. Als dat zo is heb je gelijk, maar hoewel ik van MySQL veel verwacht is dat wel heel erg grof.

Nee, autoincrement in MySQL vult de gaten niet op.

Mgoed, hoe erg is het voor je performance om bij dergelijke replies een extra update te doen? Ik heb niet het vrermoeden dat het om duizenden nieuwe replies per minuut gaat.

NMe vroeg het al, maar waarom zou je?

En ik zit tegen een DBMS aan t elullen dat geen NULL kent
In een taal die wel *NULL kent maar die gelijkstelt aan 0 of spaties afhankelijk van het datatype.
En het is niet eens iets nieuws als MySQL.

Ik ga er maar van uit dat die taal geen php is, want die kent wel null En MySQL ook, dus hiervoor is dat idd offtopic.

Dido schreef op woensdag 16 augustus 2006 @ 14:44:
Omdat dan je ORDER BY parentID, commentID niet meer werkt

Dat is wel een heel beroerd argument

Dan doe je toch zoiets?
ORDER BY COALESCE(parentid, replyid), replyid;

Of met IFNULL/ISNULL oid mocht COALESCE niet bestaan in MySQL.

[ Voor 23% gewijzigd door ACM op 16-08-2006 14:57 ]

[Gerco]

Dido schreef op woensdag 16 augustus 2006 @ 14:36:
Mgoed, hoe erg is het voor je performance om bij dergelijke replies een extra update te doen? Ik heb niet het vrermoeden dat het om duizenden nieuwe replies per minuut gaat.

Je kan ook IDs genereren in applicatiecode (GUIDs ofzo), maar die extra update query kan het probleem nooit zijn. Overigens kun je GUIDs niet gebruiken om zinnig op te sorteren, dus dat lijkt me hier geen optie.

offtopic:
En ik zit tegen een DBMS aan t elullen dat geen NULL kent
In een taal die wel *NULL kent maar die gelijkstelt aan 0 of spaties afhankelijk van het datatype.
En het is niet eens iets nieuws als MySQL.

offtopic:
RPG? met DB2/400?

[Dido]

ACM schreef op woensdag 16 augustus 2006 @ 14:57:
Nee, autoincrement in MySQL vult de gaten niet op.

Is er toch nog iets goed aan MySQL

Ik ga er maar van uit dat die taal geen php is, want die kent wel null En MySQL ook, dus hiervoor is dat idd offtopic.

offtopic:
LANSA (rpg) tegen DBII-400

edit: Goed geraden Gerco

Dat is wel een heel beroerd argument

Uiteraard zijn daar andere oplossingen voor. De vraag is of het makkelijker is om een extra update te doen of om je queries moeilijker te maken.

Dan doe je toch zoiets?
ORDER BY COALESCE(parentid, replyid), replyid;

Die levert geeft

Comment1 (replyid1)
Reply op Comment2 (parent1, replyid3)
Comment2 (replyid2)

terug. Zo eenvoudig is het nog niet op te lossen met een order by in dat geval. Het kan uitraard ongetwijfeld met parentID=NULL, maar ik denk dat dat wellicht applicatief zou moeten.

[ Voor 4% gewijzigd door Dido op 16-08-2006 15:08 ]

[ACM]

Dido schreef op woensdag 16 augustus 2006 @ 15:05:
Die levert geeft

Comment1 (replyid1)
Reply op Comment2 (parent1, replyid3)
Comment2 (replyid2)

terug. Zo eenvoudig is het nog niet op te lossen met een order by in dat geval. Het kan uitraard ongetwijfeld met parentID=NULL, maar ik denk dat dat wellicht applicatief zou moeten.

Dan doe je toch iets verkeerd... Want dat is per definitie gewoon ORDER BY parentid, replyid waarbij de parents hun eigen id als parentid hebben... maar dan impliciet, omdat ie met NULL werkt ipv de eigen id.

acm=# create table comments (parentid int, replyid int, text varchar(100));
acm=# insert into comments values(null, 1, 'parent1');
acm=# insert into comments values(1, 2, 'comment op parent1');
acm=# insert into comments values(1, 3, 'comment2 op parent1');
acm=# insert into comments values(null, 4, 'parent2');
acm=# insert into comments values(null, 5, 'parent3');
acm=# insert into comments values(5, 6, 'comment op parent3');
acm=# insert into comments values(1, 7, 'comment op parent1');
acm=# select * from comments order by coalesce(parentid, replyid), replyid;
 parentid | replyid |        text
----------+---------+---------------------
          |       1 | parent1
        1 |       2 | comment op parent1
        1 |       3 | comment2 op parent1
        1 |       7 | comment op parent1
          |       4 | parent2
          |       5 | parent3
        5 |       6 | comment op parent3
(6 rows)

[ Voor 3% gewijzigd door ACM op 16-08-2006 15:36 ]

[Dido]

offtopic:
Jawel! Ik lul uit mijn nek!
Verklaart misschien ook waarom die functie waar ik aan bezig ben nog niet werkt.

* Dido kruipt terug in zijn hok

[moozzuzz]

Cool, had nog nooit van coalesce gehoord infeite... Van mysql-docs lees ik:

Neemt eerste niet-NULL argument behalve als er enkel NULL-argumenten meegegeven worden. In het kader van het voorbeeld van ACM:

SQL:

blah ORDER BY coalesce(parentid, replyid), replyid;

wordt er dus geordend op deze manier (schitterend):

code:

coalesce-resultaat    replyid
         1                1
         1                2
         1                3
         1                7
         4                4
         5                5
         5                6

Leve ACM!

[peke]

SELECT comment, reply FROM comments LEFT JOIN replies ON replies.commentid = comments.id

Zo ff op 5 minuten dbtje gemaakt en getest, moeilijk hé.