Advanced MySQL Query Problem

brianl

I'm having a problem getting a thread listing with my forum. I only want the first post in a specific topic to show, but it's matching on every post to that specific thread_id. I tried a GROUP BY but it always returns the reply instead of the main post. Is there a way to do this in one query using my existing table setup, or do I have to re-do them?

Thanks,
Brian

mysql> select * from threads;
+-----------+----------+--------------+----------------+---------------+
| thread_id | forum_id | thread_posts | thread_date    | thread_closed |
+-----------+----------+--------------+----------------+---------------+
|         8 |       16 |            1 | 20020907140126 |             0 |
+-----------+----------+--------------+----------------+---------------+
1 row in set (0.00 sec)

mysql> select * from posts;
+---------+---------+-----------------+-----------+----------------+
| post_id | user_id | post_title      | post_text | post_date      |
+---------+---------+-----------------+-----------+----------------+
|      10 |       1 | Re: Test Thread | test text | 20020907141347 |
|       9 |       1 | Test Thread     | test text | 20020907141347 |
+---------+---------+-----------------+-----------+----------------+
2 rows in set (0.00 sec)

mysql> select * from thread_posts;
+-----------+---------+
| thread_id | post_id |
+-----------+---------+
|         8 |      10 |
|         8 |       9 |
+-----------+---------+
2 rows in set (0.00 sec)

mysql>
	SELECT t.thread_id, t.thread_posts, t.thread_closed, DATE_FORMAT(thread_date, '%m/%d/%Y %r') AS thread_date,
		   p.post_title, u.user_id, u.user_name
	  FROM threads t
 LEFT JOIN thread_posts tp
		ON t.thread_id = tp.thread_id
 LEFT JOIN posts p
		ON tp.post_id = p.post_id
 LEFT JOIN users u
		ON p.user_id = u.user_id
	 WHERE t.forum_id = $forum_id
  ORDER BY t.thread_date DESC;


WHAT I GET:
+-----------+--------------+---------------+------------------------+-----------------+---------+-----------+
| thread_id | thread_posts | thread_closed |            thread_date |      post_title | user_id | user_name |
+-----------+--------------+---------------+------------------------+-----------------+---------+-----------+
|         8 |            1 |             0 | 09/07/2002 02:01:26 PM | Re: Test Thread |       1 |     Brian |
|         8 |            1 |             0 | 09/07/2002 02:01:26 PM |     Test Thread |       1 |     Brian |
+-----------+--------------+---------------+------------------------+-----------------+---------+-----------+
2 rows in set (0.00 sec)

WHAT I WANT:
+-----------+--------------+---------------+------------------------+-----------------+---------+-----------+
| thread_id | thread_posts | thread_closed |            thread_date |      post_title | user_id | user_name |
+-----------+--------------+---------------+------------------------+-----------------+---------+-----------+
|         8 |            1 |             0 | 09/07/2002 02:01:26 PM |     Test Thread |       1 |     Brian |
+-----------+--------------+---------------+------------------------+-----------------+---------+-----------+
1 rows in set (0.00 sec)

Lars_Berg

It could probably be done by using group by and some string manipulation but I think that you are better off by adding a column to the posts table that indicates that a post is the first in a thread. If you used a dbms that supported subselects this could be done easliy in one query.

I'm also wondering why you have the posts_thread table? Surely one post can only belong to one thread?

And there is no need for using left join as far as I can see.

brianl

If I didn't have that table I'd be storing post_id in the threads table (to point to the first post in the thread) and then again I'd have thread_id in the posts table, so it's storing it back and forth. Thanks for the reply, I guess I'll just have to add a column that flags whether it's a reply or not. Something I was trying to avoid. Oh well.

[deleted]

It's not very apparent in forums like this one on phpbuilder because all the replies are just printed in the order they cam in (which is shite because you can't tell who's replying to who), but posts are always replies to other posts, so every post should have a 'replyto' or 'parent' field indicating which post this is a reply to.

The first post in a thread simply has a NULL value for the parent, because it does not have a parent.

Then you can select the threads using

SELECT threadid FROM table WHERE parentid IS NULL;

Technically you can trace the entire thread from this first post due to the parent/child relations, but this is a complex and slow operation.
It is therefore better to also store the thread-id in every reply, so you can limit your search to records that have the same thread-id.