|
|
Posted by NC on 10/21/05 22:41
Naich wrote:
>
> > > The best approach woould be to figure out a database structure that
> > > would allow you to retrieve all comments in one query in the correct
> > > order, so no PHP sorting is required.
>
> That's a possibility.
>
> > The problem if such a structure is the overhead on inserting new data.
> > And since he has a comment system, insertion could be very often. Don't
> > get me wrong I would prefer such a structure but I'm not sure whether
> > the OP get it to work or whether the insertion takes to long.
>
> There would be far more retrievals than inserts, so I think this could be
> the way to do it. Now to come up with some sort of cunning scheme...
Well, you can try this:
Table comments:
id (INT, AUTO_INCREMENT, primary key)
post_id (INT, non-unique, indexed)
parent (VARCHAR, indexed)
posted (DATETIME)
comment (TEXT or VARCHAR)
The trick would be in the `parent` field. Instead of holding only
parent's ID, you can make it hold the entire lineage; this is why
I suggest it be a VARCHAR. Let's say someone posts the first
comment to a post #23. Let's further say that this comment was
INSERTed under #488. Now your comments table looks like this:
================================
id post_id parent
================================
488 23 null
================================
Now two people post replies to comment #488, so the table looks
something like this:
================================
id post_id parent
================================
488 23 null
502 23 488
514 23 488
================================
Now someone else replies to post #502; but #502 has a parent, #488,
so the new post's parent ID becomes 488-502:
================================
id post_id parent
================================
488 23 null
502 23 488
514 23 488
586 23 488-502
================================
Two more people reply to #586:
================================
id post_id parent
================================
488 23 null
502 23 488
514 23 488
586 23 488-502
603 23 488-502-586
635 23 488-502-586
================================
Now retrieving all coments to post #23 in the correct order
is a snap:
SELECT [fields]
FROM comments
WHERE post_id = 23
ORDER BY parent, id;
The query should be fairly fast, since id, post_id, and parent
are all indexed...
Note that the parent field can also help you with visualization.
If it is null, you are deailng with a first-level comment. If
it is not null and has no dashes, it is a second-level comment.
If it contains x dashes, you are looking at (x+2) level comment.
Cheers,
NC
[Back to original message]
|