按最新回复时间排列论坛帖子 [英] Arrange forum posts by time of latest reply

问题描述

大声喊叫ORDER BY id之前，情况大不相同.

Before you shout ORDER BY id, the situation is quite different.

我被要求建立的论坛是一个留言簿论坛，未注册的用户可以在该论坛上回复并发表帖子.对帖子的回复在要回复的帖子下缩进.当然，这都是无序列表.一个简短的例子:

The forum I was asked to make is a guestbook forum, where unregistered users can reply to and make posts. Replies to a post are indented under the post being replied to. Of course, this is all in unordered lists. A short example:

• 主要帖子
• • 回复发布
  • Main post
  • • Reply to post
    • • 对回复的回复
      • • A reply to the reply
        • 另一个对main的回复

        就像 Nettuts + 的评论系统一样.在数据库中，帖子包含所有明显的内容(id，消息正文，作者，时间...)和一个replyid. Replyid基本上是指该帖子所回复的内容.如果replyid为0，则它​​是主要帖子(普通论坛中的主题.)

        It's like what Nettuts+'s comment system looks like. In the database, a post has all the obvious things (id, message body, author, time...) and a replyid. The replyid basically means what is this post a reply to. If replyid is 0, it's a main post (thread in ordinary forum means.)

        这就是我显示这些帖子的方法:首先，我调用一个函数(我们称它为showPosts)，该函数具有一个称为replyid的可选参数.默认为0.

        And that's how I go around displaying these posts: At first, I call a function (let's call it showPosts) which has an optional param called replyid; defaults to 0.

        在showPosts中，我以关联数组的形式从数据库中获取所有回复ID等于参数的所有post，并使用showPosts的结果填充数组中的posts字段，然后将showid传递给showPosts .在showPosts的结尾，我返回了该关联数组.如果不清楚，请参见以下代码段:

        In showPosts I grab all posts with a replyid equal to the param from the database in an associative array, and populate the posts field in the array with the results of showPosts, and I pass to showPosts the id of the post. In the end of showPosts I return that associative array. If this was unclear, here's the snippet:

         function showPosts($postid = 0) {
                $query = query("SELECT * FROM posts WHERE replyid='$postid'");
                $r = array();
                $i = 0;
                while (@$row = $query->fetch_assoc()) {
                    $r[$i] = $row;
                    $r[$i]['posts'] = showPosts($row['id']);
                    ++$i;
                }
                return $r;
            }
        

        一切正常，如预期的那样，但是我为这个问题所困扰:当用户回复旧帖子时，我希望该帖子首先显示，就像您在论坛主题中一样.

        Everything's working great, like expected, but I am stomped by this problem: When a user replies to an old post, I want that post to show first, just like you have in forum threads.

        我已经考虑过要在数据库中创建一个名为lastChanged的字段，并且每当用户发布新答复时，它就会一直沿帖子链向上移动，并将每个lastChanged值更改为发布时间.但是，这似乎只是浪费内存并可能浪费时间.我还考虑过将主线程与答复分开，但是这会使事情更加复杂，而且我喜欢在线程和帖子之间不做任何改变的美，因为任何东西都可以是线程或帖子.

        I've already thought about making a field in the database called lastChanged, and whenever a user posts a new reply, it goes all the way up the posts chain and changes each of their lastChanged value to the time of posting. However, this seemed just a waste of memory and a possible time killer. I've also thought of seperating main threads from replies, but that'd make things a little more complicated on the mind, and I like the beauty of not making a difference between a thread and a post, since anything can be either a thread or a post.

        请注意，数据库是MySQL，后端是用php编写的(当然，使用MySQLi与MySQL进行交互).

        Just so you know, the database is MySQL and backend written in php (and of course, interacting with MySQL using MySQLi.)

        在此先感谢您，如果有任何不清楚的地方，我们表示歉意.

        Thanks in advance, and sorry if anything is unclear.

        数据库的结构，根据要求.

        The db's structure, as requested.

        id      |  int(11)
        title   |  varchar(100)
        author  |  varchar(100)
        body    |  text
        replyid |  int(11)
        time    |  datetime
        

        推荐答案

        Hmm ..考虑到读取列表比写入要频繁得多.而且考虑到读取涉及的数据要多得多，所以我宁愿增加写入时间的负担.

        Hmm.. considering that reading the list is executed far more often than writing. And also considering that reading involves far more data I would prefer to put the load on write time.

        在根级别上使用lastChanged解决方案，您还可以通过简单的SQL查询来读取更少的行，而不用读取x的行数来完成列表.

        With the lastChanged solution on root level you can also do the list with reading far less rows with a simple SQL Query instead of reading x numbers of rows.

        Sams解决方案的缺点是，您需要一个额外的GROUP BY，这会导致一个临时表，这会降低SQL Server的速度.

        Sams solution has the disadvantage, that you need a additional GROUP BY, which leads to a temporary table, which slows down your sql server.

        这篇关于按最新回复时间排列论坛帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！