mysql - 从具有父子关系的留言板中查询最后的 n,15 个主题

Question

我已经将我的数据库设计从单独的主题/帖子表更改为具有父子关系的单个表。

表格如下所示:

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel_id` int(11) DEFAULT '1',
  `parent_id` int(25) NOT NULL,
  `date` int(12) NOT NULL,
  `author` int(25) NOT NULL,
  `message` text NOT NULL,
  `bbcodes_enabled` int(1) NOT NULL,
  `title` varchar(255) NOT NULL,
  `state` int(2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `article_id` (`parent_id`),
  KEY `author` (`author`),
  FULLTEXT KEY `message` (`message`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=401614 ;

现在我想创建一个主题列表。如果 parent_id 等于 0，则该行表示一个新主题。否则 parent_id 包含根元素的 ID。

这个问题在某些方面与我的另一个问题相似，但我认为新模式新问题...

如果有人为某个主题提交新帖子，则新帖子的日期是该主题的 last_activity_date。

我想要的是找到所有主题 (limit x,offset) 并按 last_activity_date 对它们进行排序，该日期可以在父元素的最后提交的帖子中找到。

主题链接到一个 channel ，相当于普通留言板中的一个类别。

大多数情况下它应该看起来像这样但是..按最后一篇文章的日期排序。

SELECT p.id, p.date, p.author, p.title, p.state, a.name, a.avatar, a.color 
FROM posts p
INNER JOIN authors a
ON a.id = p.author
AND p.channel_id = 126
AND p.parent_id = 0
ORDER BY p.id 
DESC

我对 mysql 的理解还不错。但我完全不知道该怎么做。我遇到过几次这种情况，直到现在都没有找到解决办法。只有解决方法，例如使用两个或三个查询..

我希望，我真的希望..有人可以帮助我解决这种情况。

我在这里的临时解决方案是一个 3 查询( Not Acceptable )，例如:

查询 1:

SELECT DISTINCT parent_id 
FROM posts 
WHERE channel_id = x 
ORDER BY date DESC

循环结果并运行这两个查询:

查询 2:

SELECT date as last_ativity from posts where parent_id = x DESC LIMIT 1

查询 3:

SELECT * FROM posts WHERE id = x

想要的结果(在网站上)看起来像这样:

1. TopicTitle | last activity 1 second ago
2. TopicTitle | last activity 35 seconds ago
3. TopicTitle | last activity 1 min ago
4. ...
...

Answer 1

如果我正确理解要解决的问题，您可以通过对每个 parent_id< 返回最近的 date 的子查询执行 LEFT JOIN 来完成。 LEFT JOIN 用于主题没有关联的帖子，因此没有最近的事件。

SELECT
  p.id,
  p.author,
  p.title,
  /* other fields from posts to related to the topic...*/
  /* Choose the joined date if non-null, otherwise the topic parent date */
  COALESCE(d.last_activity_date, p.date) AS last_activity_date,
FROM
  posts p
  LEFT JOIN (
    /* return latest date per parent_id */
    SELECT parent_id, MAX(date) AS last_activity_date
    FROM posts
    GROUP BY parent_id
    /* and join back to the main posts table */
  ) d ON p.id = d.parent_id
/* Only get the top-level threads */
WHERE 
  p.parent_id = 0
  AND p.channel_id = <value>
ORDER BY last_activity_date DESC
LIMIT 0, 15

将日期格式化为看起来像“35 秒前” 最好在您的应用程序代码中完成，而不是在 SQL 查询中完成，许多语言都有专门用于生成“人类可读”时间的库那种。