选择所有具有最新帖子，用户和主题信息的类别 [英] Select all categories with latest post, user, and topic information

问题描述

我正在为Web项目创建自定义论坛.我有带有ID的类别，带有ID和类别的主题，以及带有ID和主题以及用户ID的帖子.我想做的是显示类别列表，其中包含类别表中的数据以及该类别中最新帖子的帖子表中的数据，该帖子的关联用户的数据以及该主题的一些数据与该最新帖子相关联.

I am working on a custom forum for a web project. I have categories with id, topics with id and category, and posts with id and topic and user id. What I'm trying to do is display a list of the categories with data from the categories table along with data from the posts table for the newest post in that category, data for that posts's associated user, as well as some data for the topic associated with that latest post.

我一直在想办法弄清楚查询，但我只是对复杂的mysql查询没有足够的了解，以至于不知道在这里使用哪种模式或技术.这是我到目前为止的内容:

I've been banging my head on the wall trying to figure the query out, but I just don't have a good enough understanding of complex mysql queries to know what pattern or technique to use here. Here is what I have so far:

SELECT u1.*, fp1.*, ft1.*, fc1.* from forum_posts AS fp1
LEFT JOIN users AS u1 ON u1.id = fp1.post_by
LEFT JOIN forum_topics AS ft1 on ft1.id = fp1.post_topic
LEFT JOIN forum_categories AS fc1 on fc1.id = ft1.topic_cat
GROUP BY fc1.id
ORDER BY fp1.id ASC;

但这不会返回我想要的结果.问题在于尝试获取每个类别的最新帖子以及该帖子的关联主题.

But this does not return the results I'm looking for. The problem is in trying to get the newest post for each category and the associate topic for that post.

这是每个表的数据库结构:

Here is the DB structure for each table:

forum_categories

forum_categories

+-----------------+---------------------+------+-----+---------+----------------+
| Field           | Type                | Null | Key | Default | Extra          |
+-----------------+---------------------+------+-----+---------+----------------+
| id              | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| cat_name        | varchar(255)        | NO   | UNI | NULL    |                |
| cat_description | varchar(500)        | NO   |     | NULL    |                |
| cat_views       | bigint(20) unsigned | YES  |     | 0       |                |
| status          | tinyint(1)          | NO   |     | 1       |                |
+-----------------+---------------------+------+-----+---------+----------------+

forum_topics

forum_topics

+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| topic_subject | varchar(255)        | NO   |     | NULL    |                |
| topic_date    | datetime            | NO   |     | NULL    |                |
| topic_cat     | bigint(20) unsigned | NO   | MUL | NULL    |                |
| topic_by      | bigint(20) unsigned | NO   | MUL | NULL    |                |
| topic_views   | bigint(20) unsigned | YES  |     | 0       |                |
+---------------+---------------------+------+-----+---------+----------------+

论坛帖子

+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| post_content | text                | NO   |     | NULL    |                |
| post_date    | datetime            | NO   |     | NULL    |                |
| post_topic   | bigint(20) unsigned | NO   | MUL | NULL    |                |
| post_by      | bigint(20) unsigned | NO   | MUL | NULL    |                |
+--------------+---------------------+------+-----+---------+----------------+

用户

+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_type | varchar(50)         | YES  |     | NULL    |                |
| email     | varchar(255)        | NO   | UNI | NULL    |                |
| username  | varchar(255)        | YES  | UNI | NULL    |                |
| password  | char(60)            | NO   |     | NULL    |                |
| image     | text                | YES  |     | NULL    |                |
| status    | tinyint(1)          | NO   |     | 1       |                |
+-----------+---------------------+------+-----+---------+----------------+

这是我要实现的输出的图像. 类别"显示来自forum_categories表的数据，最近"下方是最新帖子的用户，帖子和主题数据:

Here is an image of the output I'm trying to achieve. "Categories" shows the data from the forum_categories table and under "Recent" is the user, post and topic data for the latest post:

请帮帮我.谢谢.

推荐答案

虽然将所有表连接在一起以计算出每个帖子的主题，类别和用户非常简单，但是您还需要一个额外的步骤-您需要加入一个子查询，该子查询检索每个类别的max(post_id).

While it's simple enough to join all the tables together to work out the topic, category and user for each post, you also need one additional step - you need to join to a subquery that retrieves the max(post_id) per category.

这是您可以执行此操作的一种方法:

Here's one way you can do that:

select fc.cat_name, fc.cat_description, fc.cat_views, u.username, fp.post_date, ft.topic_subject
  from forum_categories fc
    inner join forum_topics ft
      on fc.id = ft.topic_cat
    inner join forum_posts fp
      on fp.post_topic = ft.id
    inner join users u
      on fp.post_by = u.id
    inner join (
      select topic_cat, max(fp.id) most_recent_post
        from forum_topics ft
          inner join forum_posts fp
            on fp.post_topic = ft.id
      group by topic_cat
    ) q
      on q.topic_cat = ft.topic_cat
        and fp.id = q.most_recent_post;

有一个演示可以在此处使用: http://sqlfiddle.com/#!9/3736b/1

There's a demo you can play with here: http://sqlfiddle.com/#!9/3736b/1

这篇关于选择所有具有最新帖子，用户和主题信息的类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！