Search code examples
phpmysqlforums

Select all categories with latest post, user, and topic information

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.

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

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

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

forum_posts

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

users

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

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:

The output I'm trying to achieve

Please help me out. Thank you.

Solution

  • 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;

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