存储树数据的快速关系方法(例如文章的线程评论) [英] Fast Relational method of storing tree data (for instance threaded comments on articles)

问题描述

我有一个 cms 来存储对文章的评论.这些评论既可以是线程化的，也可以是非线程化的.虽然从技术上讲，它们是相同的，只是在没有线程时回复列留空.我的应用程序适用于 sqlLite、MySQL 和 pgsql，因此我需要相当标准的 SQL.

I have a cms which stores comments against articles. These comments can be both threaded and non threaded. Although technically they are the same just with the reply column left blank when it's not threaded. My application works on sqlLite, MySQL and pgsql so I need fairly standard SQL.

我目前有一个评论表

comment_id
article_id
user_id
comment
timestamp
thread (this is the reply column)

我的问题是弄清楚如何最好地表示数据库中的线程评论.也许在一个单独的表中支持没有内容的树集和一个简单的表来保存文本?也许就像它已经存在的那样?也许另一种方式?

My question is to figure out how to best represent the threaded comments in the database. Perhaps in a separate table that supports the tree set without the content and a simple table to hold the text? Perhaps in the way it already is? Perhaps another way?

如果评论是非线程化的，我可以轻松地按时间戳排序.

If the comments are un-threaded I can easily just order by the timestamp.

如果它们是线程的，我会这样排序

If they are threaded I sort like this

ORDER BY SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1))

正如您从 ORDER BY 中看到的那样，注释查询永远不会使用索引作为基于函数的索引，它只存在于 Oracle 中.帮助我拥有闪电般的快速评论页面.

As you can see from the ORDER BY, the commenting queries will not ever use an index as function based indexes only really live in Oracle. Help me have lightening fast comment pages.

推荐答案

我真的很喜欢 Drupal 解决这个问题的方式.它为每个评论分配一个线程 id.对于第一条评论，此 id 从 1 开始.如果对此评论添加了回复，则会为其分配 id 1.1.评论 1.1 的回复被赋予线程 ID 1.1.1.注释 1.1 的兄弟被赋予线程 ID 1.2.你明白了.添加评论后，只需一个查询即可轻松计算这些线程 ID.

I really like how Drupal solves this problem. It assigns a thread id to each comment. This id starts at 1 for the first comment. If a reply is added to this comment, the id 1.1 is assigned to it. A reply to comment 1.1 is given the thread id 1.1.1. A sibling of comment 1.1 is given the thread id 1.2. You get the idea. Calculating these thread ids can be done easily with one query when a comment is added.

当线程被渲染时，属于该线程的所有评论都在单个查询中被提取，按线程 id 排序.这为您提供了升序的线程.此外，使用线程 id，您可以找到每个评论的嵌套级别，并相应地缩进.

When the thread is rendered, all of the comments that belong to the thread are fetched in a single query, sorted by the thread id. This gives you the threads in the ascending order. Furthermore, using the thread id, you can find the nesting level of each comment, and indent it accordingly.

1
1.1
1.1.1
1.2
1.2.1

有几个问题需要解决:

• 如果线程 id 的一个组成部分增长到 2 位数，则按线程 id 排序不会产生预期的顺序.一个简单的解决方案是确保线程 ID 的所有组件都用零填充以具有相同的宽度.
• 按线程 ID 降序排序不会产生预期的降序.

Drupal 使用称为 vancode 的编号系统以更复杂的方式解决了第一个问题.至于第二个问题，在按降序排序的线程id后面加一个反斜杠(ASCII码高于数字)来解决.您可以通过检查 评论模块(见comment_get_thread函数前的大评论).

Drupal solves the first issue in a more complicated way using a numbering system called vancode. As for the second issue, it is solved by appending a backslash (whose ASCII code is higher than digits) to thread ids when sorting by descending order. You can find more details about this implementation by checking the source code of the comments module (see the big comment before the function comment_get_thread).

这篇关于存储树数据的快速关系方法(例如文章的线程评论)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！