[RESOLVED] Database Query Problem - Select Most Recent Distinct

HairyArse

Got a problem that has probably been answered a million times before I'm sure, but I can't quite put other people's solutions into the context of my database.

I have two tables:

tbl_forum_threads

thread_id
thread_author
thread_title
thread_category_id
thread_stickied

tbl_forum_replies

reply_id
thread_id
reply_author
reply_text
reply_date

As you can probably guess this is for a forum I am working on.

I am trying to get my main forum page to display threads in order of most recently posted into. The problem I have at the moment is that the query below actually sorts them into the order they were created and not the order they were posted into.

The query I have at the moment refers to some other tables, but for the sake of the tables above and to keep things simple, can anyone get my query to do what I want it to?

SELECT a.*, UNIX_TIMESTAMP(b.reply_date) AS reply_date, c.category_name, d.user_id, d.username 

FROM

tbl_forum_threads a, tbl_forum_replies b, 
tbl_forum_categories c, tbl_users d 

WHERE 

a.thread_author = d.user_id 
AND a.thread_id = b.thread_id 
AND a.thread_category_id = c.category_id 
GROUP BY a.thread_id ORDER BY a.thread_stickied ASC, reply_date DESC

sneakyimp

I'm not sure if this is going to work for threads that don't have any replies to them. also, i'm not sure what data bits you need. you might be able to simplify this a bit.

SELECT a.*, UNIX_TIMESTAMP(b.reply_date) AS reply_date, c.category_name, d.user_id, d.username 

FROM

tbl_forum_threads a, tbl_forum_replies b, 
tbl_forum_categories c, tbl_users d 

WHERE 

a.thread_author = d.user_id 
AND a.thread_id = b.thread_id 
AND a.thread_category_id = c.category_id 
GROUP BY a.thread_id ORDER BY b.reply_date DESC

HairyArse

Hi sneaky thanks for your help alas that doesn't seem to do anything differently to my own query.

The query you posted lists them ordered by thread_id and not reply_date even though the query specifically tells it to.

🙁

sneakyimp

try this?

SELECT a.*, UNIX_TIMESTAMP(b.reply_date) AS reply_date, c.category_name, d.user_id, d.username 

FROM

tbl_forum_replies b, tbl_forum_threads a,
tbl_forum_categories c, tbl_users d 

WHERE 
b.thread_id = a.thread_id
AND a.thread_author = d.user_id 
AND a.thread_category_id = c.category_id 
GROUP BY b.thread_id ORDER BY b.reply_date DESC

HairyArse

That still gives the same results.

Now you can see why I'm finding it so frustrating.

🙁

Lars_Berg

SELECT a.*, 
       c.category_name, 
       d.user_id, 
       d.username,
  (select max(reply_date)
     from tbl_forum_replies
    where thread_id = a.thread_id) as lastReply
  FROM tbl_forum_threads a, 
       tbl_forum_categories c, 
       tbl_users d 
 WHERE a.thread_author = d.user_id 
   AND a.thread_category_id = c.category_id 
 ORDER BY a.thread_stickied ASC, 
       lastReply DESC

HairyArse

Lars! Thanks matey, that worked an absolute treat!

You have no idea how pleased I am.

😃

HairyArse

One other thing, can anyone tell me how to modify Lars' excellent query so that reply_id is also grabbed from the table tbl_forum_replies.

I need this so I can add a 'latest post by' column to my table.

Lars_Berg

SELECT a.*, 
       c.category_name, 
       d.user_id, 
       d.username,
       b.reply_date,
       b.reply_id
  FROM tbl_forum_threads a, 
       tbl_forum_replies b,
 WHERE a.thread_author = d.user_id 
   AND a.thread_category_id = c.category_id 
   and a.thread_id = b.thread_id
   and b.reply_date in 
       (select max(reply_date)
          from tbl_forum_replies
         where thread_id = b.thread_id)
 ORDER BY a.thread_stickied ASC, 
       reply_date DESC

HairyArse

Bit of a bump.

Lars got this query working for me a while ago, the problem is that it's become too slow all of a sudden. The query takes 5 seconds to run which is way too long, can anyone tell me a better way of achieving the same results?

Piranha

You have started a new topic with this, is it not enough to have one topic active with the problem? The new topic can be found here.

HairyArse

Piranha, apologies for causing any inconveniences.

Yes I realise I have this discussion spread over two threads, it's just that this thread contains a more detailed explanation of what I'm trying to get at as well as outlining some of the processes the board went through to get there in the first place. I guess I should have copied and pasted but instead I decided to bump it instead.