MySQL Parent Child Relations

[The Little Guy]

Is this a good way to do this?

SELECT c1.* 
FROM network_comments c1
LEFT JOIN network_comments c2
ON(c1.id = c2.comment_id)
WHERE c1.feed_id = 9 
ORDER BY c1.feed_id, c2.comment_id, c1.`date`

 

I have item comments, and comment comments in the same table.

 

------------------------------------------------

Item Comment: Comment on an item

Comment Comments: Comments on a comment of an item

------------------------------------------------

 

The above doesn't work,

 

I would like it to order it first by the comment_id, then if a comment has any sub comments (c1.id = c2.comment_id), have that under it.

 

So basically, it looks like a directory listing:

id+comment_id

1+0

  3+1

      4+3

2+0

  5+2

  6+2

      7+6

 

 

So, to summarize the above, the second number matches the parent of the first number. How can I get my MySQL query to make a list in this order?

 

 

If this doesn't make sense please let me know!

[fenway]

I don't see any reference to item comments in your query.

[The Little Guy]

I put the item comments and the comment comments in one table. Is that a bad things?

[The Little Guy]

I don't see any reference to item comments in your query.

 

c1.feed_id is the root item that will have comments.

 

If anyone has used Google Wave, they have something similar, you can start a wave, then someone can comment your message, and someone else can comment the person that commented you, or they can comment you instead, etc.

[ignace]

I don't see any reference to item comments in your query.

 

c1.feed_id is the root item that will have comments.

 

If anyone has used Google Wave, they have something similar, you can start a wave, then someone can comment your message, and someone else can comment the person that commented you, or they can comment you instead, etc.

 

Using an Adjacency List for something like this won't work because you would somehow have to dynamically write JOIN statements to show all comments, for 3 levels deep you'd write:

 

SELECT ...

FROM table t1

JOIN t2 ON t1.id = t2.parent_id

JOIN t3 ON t2.id = t3.parent_id

..

 

A far better approach would be to use a Closure Table as explained by Bill Karwin:

[The Little Guy]

I am confused.

 

After looking at that, I then looked at this: http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree#192462

 

To me, it looks like we are storing the same data twice.

 

Couldn't you do a mysql loop, and go until their are no rows left to add?

[fenway]

It really depends on how many levels deep you're storing, how large the table is going to be, and how often it's going to be changed, and it what ways.

[The Little Guy]

I have no idea how deep they will be, I was hoping they could go as deep as they wanted, with no limit...

[fenway]

Then closure paths are probably the best approach -- though really, really unintuitive.

[The Little Guy]

Using the table I have in my attachment, how would I do it?

 

- comment_id is a child of id

- if comment_id = 0 than it is a root item of the feed_id

- feed_id can have 1+ root items

 

[attachment deleted by admin]

[fenway]

There was a link to a URL that discusses this technique.