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

问题描述

我有一个cms存储对文章的评论。这些注释可以是线程和非线程。虽然技术上他们是一样的只是答复列留空，当它没有线程。我的应用程序适用于sqlLite，MySQL和pgsql，所以我需要相当标准的SQL。

我目前有一个注释表

  comment_id 
 article_id 
 user_id 
注释
时间戳
线程（这是回复列）
  

我的问题是找出如何最好地表示数据库中的线程注释。也许在一个单独的表中支持没有内容的树集和一个简单的表来保存文本？也许在这个方式已经是？也许另一种方式？

如果评论是非线程的，我可以轻松地按时间戳订单。

如果他们是线程我排序这样

  ORDER BY SUBSTRING（c.thread，1，（LENGTH（c.thread）） 1））
  

从ORDER BY可以看出，评论查询不会使用索引因为基于函数的索引只能真正存在于Oracle中。帮助我有更轻松的快速评论页。

解决方案

我真的很喜欢 Drupal 解决了这个问题。它为每个注释分配一个线程ID。该ID从第一个注释开始为1。如果在此评论中添加回复，则会分配id 1.1 。给予评论 1.1 的回复给予线程ID 1.1.1 。评论 1.1 的兄弟姐妹给了线程ID 1.2 。你得到的想法。当添加注释时，可以通过一个查询来轻松计算这些线程ID。

当线程呈现时，所有属于线程的注释都将被读取单个查询，按线程ID排序。这给你的线程按升序排列。此外，使用线程ID，您可以找到每个注释的嵌套级别，并相应缩进。

  1 
 1.1 
 1.1.1 
 1.2 
 1.2.1 
  

有一些问题要解决：

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


• 按降序排列的线程ID不会产生预期的降序。

Drupal使用称为vancode的编号系统，以更复杂的方式解决了第一个问题。对于第二个问题，通过在按顺序排序时将反斜杠（其ASCII代码高于数字）附加到线程ID来解决。您可以通过检查评论模块（见函数comment_get_thread之前的大注释）。

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.

I currently have a comment table

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))

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.

解决方案

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.

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

There are a few issues to sort out:

• If one component of the thread id grows to 2 digits, sorting by thread id will not produce the expected order. An easy solution is ensuring that all components of a thread id are padded by zeros to have the same width.
• Sorting by descending thread id does not produce the expected descending order.

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屋！