在具有JOIN的闭合表上运行SELECT查询吗? [英] Running a SELECT query on a closure table with a JOIN?

问题描述

我有一个应用程序设置，该应用程序将嵌套的评论附加到帖子上.我决定使用关闭表方法(幻灯片40)进行评论由于硬盘空间多么便宜，而查询和管理树形结构看起来却如此容易.但是，我遇到了一个问题.我似乎无法弄清楚如何根据帖子ID而不是祖先ID(幻灯片49)来抓取树路径.

I have an app set up which has nested comments attached to posts. I decided to use the closure table method (slide 40) for the comments due to how cheap hard disk space is versus how easy it seems to be to query and manage the tree structure. However, I'm running into a problem. I can't seem to figure out how to grab the tree path based on the post ID, not the ancestor ID (slide 49).

我的数据库结构如下:

table: comment_paths
--------------------
parent_id (fk on comments.id)
child_id  (fk on comments.id)
depth

table: comments
---------------
id
parent_id (fk on comments.id)
post_id   (fk on posts.id)
text

table: posts
---------------
id
name

如果我像幻灯片所示那样事先知道了parent_id，那么抓住树很简单:

If I know the parent_id beforehand, as in the slideshow, it's simple to grab the tree:

SELECT c.*, p.*
FROM comments AS c
JOIN comment_paths AS p
ON c.id = p.child_id
WHERE p.parent_id = 1

但是，我事先不知道parent_id.我只知道post_id.建立数据库的方式中，与一个帖子相关联的树不只一棵:

However, I don't know the parent_id beforehand; I only know the post_id. The way the database is set up, there is more than one tree associated with a post:

                 [post]
-----------------------------------------
[comment]       [comment]       [comment]   depth: 0
    |                               |
 [reply]                         [reply]    depth: 1
  |   |
 [r] [r]                                    depth: 2

我最初的解决方案是这样的查询:

My initial solution was a query like this:

SELECT c.*, p.*
FROM comments AS c
JOIN comment_paths AS p
ON c.id = p.child_id
WHERE p.parent_id IN
    (SELECT id FROM comments WHERE parent_id IS NULL AND post_id = 6)

它返回正确的数据，但是我感觉像这样的嵌套SELECT是不正确的.有更好的方法吗?

It returns the correct data, but I feel like a nested SELECT like that isn't correct. Is there a better way to do this?

谢谢！

推荐答案

我真的没有想到更好的方法.我唯一要做的更改是使用EXISTS而不是IN:

I can't really think of a better way. The only change I would make is to use EXISTS instead of IN:

SELECT c.*, p.*
FROM comments AS c
JOIN comment_paths AS p
ON c.id = p.child_id
WHERE EXISTS
(SELECT * FROM comments c2 WHERE p.parent_id = c2.id AND c2.parent_id IS NULL AND c2.post_id = 6)

我很想看看是否确实有更好的方法.

I'm interested to see if there actually is a better method though.

更新:

我不确定您使用的是什么RDMS.但是，如果递归查询可用，我想您可以完全摆脱comments_path表:

I'm not sure what RDMS you're using. But, if recursive queries are available, I'm thinking you could get away from the comments_path table entirely:

;with cte as(
select c.*, 0 as depth
from comments c
where c.post_id = 6 and c.parent_id is null
union all
select c.*, cte.depth + 1
from comments c
join cte on c.parent_id = cte.id)

select * from cte

这篇关于在具有JOIN的闭合表上运行SELECT查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！