Order on articles in another table

Question

I created a feature in which users can launch new topics (similar to forums). Currently, a topic request on a page is as follows:

$q = "SELECT ".TBL_COMMUNITYTHREADS.".title, ".TBL_COMMUNITYTHREADS.".id, 
    ".TBL_COMMUNITYTHREADS.".date, ".TBL_COMMUNITYTHREADS.".author, ".TBL_USERS.".username FROM ".TBL_COMMUNITYTHREADS." 
        INNER JOIN ".TBL_USERS." ON ".TBL_COMMUNITYTHREADS.".author = ".TBL_USERS.".id
        WHERE type = '$type'
        ORDER BY date DESC LIMIT $offset, $rowsperpage ";

Tables are constants, and offset and rowperpage are variables passed to limit the number of posts per page.

Currently, all topics are sorted by date. I want to be ordered with the latest answer. Like forums, when the answer within a topic is the newest, this topic will go up.

Topics are stored in tbl_communitythreads and responses are in tbl_communityreplies.

How can I order them with the latest answer.

They are thread bound in tbl_communityreplies. Also in this date column.

Thanks for reading, I just can't think of how to do this.

Answer 1

:

SELECT  c.title, c.id, c.date, c.author, u.username,
        (
        SELECT  MAX(reply_date)
        FROM    tbl_communityreplies cr
        WHERE   cr.thread = c.id
        ) AS last_reply
FROM    TBL_COMMUNITYTHREADS c
JOIN    TBL_USERS u
ON      u.id = c.author
ORDER BY
        last_reply DESC, c.id DESC
LIMIT   $offset, $rowsperpage

:

SELECT  c.title, c.id, c.date, c.author, u.username
FROM    (
        SELECT  cr.thread, cr.reply_date, cr.id
        FROM    tbl_communityreplies cr
        WHERE   (cr.last_reply, cr.id) = 
                (
                SELECT  last_reply, id
                FROM    tbl_communityreplies cri
                WHERE   cri.thread = cr.thread
                ORDER BY
                        thread DESC, last_reply DESC, id DESC
                )
        ORDER BY
                last_reply DESC, id DESC
        LIMIT   $offset, $rowsperpage
        ) q
JOIN    TBL_COMMUNITYTHREADS c
ON      c.id = q.thread
JOIN    TBL_USERS u
ON      u.id = c.author
ORDER BY
        q.reply_date DESC, q.id DESC

$offset, .

:

CREATE INDEX ix_communitythreads_replydate_id ON TBL_COMMUNITYTHREADS (reply_date, id)
CREATE INDEX ix_communitythreads_thread_replydate_id ON TBL_COMMUNITYTHREADS (thread, reply_date, id)

.

Answer 2

, .