0

The question itself might be written wrong so if anyone have better idea how to define it, please, edit the question.

Structure

Only important columns.

comments

  • comment_id
  • comment_parent_id
  • user_id
  • comment_text

posts

  • post_id
  • user_id
  • post_text

comments_to_post (comments_to_foos, comments_to_bars, ...)

  • comment_id
  • post_id (foo_id, bar_id, ...)

comments_to_post has only those connections to comments where comment_parent_id on comments is NULL since every other comment is just a child of another comment.

I'm struggling with query which in results will give me comments assigned to specific post (by post_id) with its children.

I tried to select from comments and join comments_to_post where post_id = ? but no wonder I got only comments with NULL comment_parent_id since only them are connected via comments_to_post.

How to add to the result also those comments which are children?

Example data:

comments

comment_id comment_parent_id
1          NULL
2          1
3          1
4          NULL
5          4

posts

post_id
10
20

comments_to_posts

comment_id post_id
1          10
4          20

Expected result

... where post_id = 10

comment_id
1
2
3
TheFrost
  • 1,265
  • 2
  • 15
  • 29
  • Show us some relevant data and the results you want. – Mihai Sep 08 '13 at 20:25
  • 1
    I would add a post_id field to the *comments* table, so you can drop the *comments_to_post* table. It saves you from recursive searches... – Marty McVry Sep 08 '13 at 20:34
  • @MartyMcVry, then I should add columns for every table I want have comments for? In real example that would be 5 columns. – TheFrost Sep 08 '13 at 20:39
  • And are you sure you need a tree hierarchy on the comments? It would be easier to have a `post_comment_number` which will be assigned numbers 1,2,3,... for the 1st, 2nd, 3rd, ... comment for every post. Unless you need comments on comment, too. – ypercubeᵀᴹ Sep 08 '13 at 23:17
  • @ypercube, yes, I'm sure. I like the idea of comments presented as those on YouTube. List with possibility of seeing comment you respond to. – TheFrost Sep 09 '13 at 01:27

2 Answers2

1

You should add field post_id to table comments (in this case you do not need table comments_to_posts). Only in this case you will be able to select all comments and sub-comments with one database request.

Also, if you want to have some data in a tree structure (comments, menus, etc.), I advise you to read about http://en.wikipedia.org/wiki/Nested_set_model. It is a very interesting and elegant approach to solve this issue.

Alex
  • 1,605
  • 11
  • 14
  • Same approach is also explained on [this page](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/). – Marty McVry Sep 08 '13 at 20:47
  • @Sanja, I understand nesting, I understand joining tables, but I was trying to connect both approaches in one query. And you just ruined my world saying it is impossible, are you happy now? But, to be serious, what if comments are available to posts, news, articles and so on? Should I add all of them as references, directly to the comments table? As post_id, news_id, article_id with NULL possibility? – TheFrost Sep 08 '13 at 20:53
  • In this case I would add 2 columns: parent_id, parent_type and create an index on both of them. In this case if you will add some new category that will also have comments (for example, Questions), you will not need to add new columns. – Alex Sep 08 '13 at 20:59
  • @Sanja, multiple foreign key on one column is impossible. So `parent_id` cannot be linked to posts, news, articles, ..., even if it depends on `parent_type`. – TheFrost Sep 08 '13 at 21:07
  • I did not say foreign keys. Add just an index to speed up database request. You can create an index on several columns. – Alex Sep 08 '13 at 21:19
  • While looking for some more help I, by coincidence, found the solution to my *comments* problem: http://stackoverflow.com/questions/5537779/multiple-tables-need-one-to-many-relationship The solution uses @MartyMcVry proposition instead of my cross-reference tables. – TheFrost Sep 08 '13 at 22:31
  • While sleeping I thought out one more approach. Let's take your DB structure. Use nested sets, but as parent ID use top comment ID. Then you will be able to select all comments with query `SELECT * FROM comments WHERE parent_comment_id IN (SELECT comment_id FROM comments_to_posts WHERE post_id = N);` and have multilevel comments. I mean, each comment tree is a separate nested set. And if you will need to add new types of commentable objects with foreign keys, then use tables like `comments_to_posts`. – Alex Sep 09 '13 at 06:58
1

Update -

You can do this without UNION as shown below. Note that both approaches will work only for single level comment thread.

SELECT c.*
FROM comments_to_post ctp , comments c
WHERE ctp.post_id = 1
  AND ( c.comment_id = ctp.comment_id OR c.comment_parent_id = ctp.comment_id)

Get the parent comments first, then get the children, and UNION them all. See DEMO fiddle. 

SELECT c.*
FROM comments_to_post ctp , comments c
WHERE ctp.post_id = 1
  AND c.comment_id = ctp.comment_id

UNION ALL

SELECT c.*
FROM comments_to_post ctp , comments c
WHERE ctp.post_id = 1
  AND ctp.comment_id = c.comment_parent_id
ORDER BY comment_id
Praveen Lobo
  • 6,956
  • 2
  • 28
  • 40