php – 怎样行使MySQL Join通过前次回覆对论坛帖子举办排序？

我在为此编写查询时碰着了一些贫困.我想我有根基的逻辑,固然我也许没有.我想要做的是按照用户正在查察的主板获取全部线程,然后按照前次回覆的时刻对这些线程举办排序.查询不返回任何错误,它只获取最近更新的线程.

这是我的查询：

$query = "
            SELECT
            t.child_id,t.thread_id,m.thread_id,m.message_id,m.date_posted
            FROM forum_threads AS t
            LEFT JOIN forum_messages AS m ON t.thread_id = m.thread_id
            WHERE t.child_id = ".$board_id."
            ORDER BY m.date_posted DESC
            LIMIT ".$starting.",".$this->user['results_per_page'];

这是要返回的查询：

            SELECT t.child_id,m.date_posted   
            FROM forum_threads AS t   
            LEFT JOIN forum_messages AS m   
            ON t.thread_id = m.thread_id   
            WHERE t.child_id = 2   
            ORDER BY m.date_posted DESC LIMIT 0,15 

更新

我试图回收ethrbunny提议的设法,尽量我完全迷失了导致它返回错误的缘故起因.

$query = "
            SELECT
            t.board_id,t.thread_id
            FROM forum_threads AS t
            LEFT JOIN (
            SELECT m.thread_id,m.message_id
            FROM forum_messages AS m
            WHERE m.thread_id = t.thread_id
            ORDER BY m.message_id DESC
            LIMIT 1
            ) AS q
            WHERE t.board_id = ".$board_id."
            ORDER BY q.date_posted DESC
            LIMIT ".$starting.",".$this->user['results_per_page'];

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE t.board_id = 4 ORDER BY q.date_posted DESC LIMIT' at line 11

SELECT ...,MAX(m.date_posted) AS latest_reply ...

GROUP BY t.thread_id
ORDER BY latest_reply DESC ...

可是为什么date_posted是一个TEXT？不该该是日期时刻,可能也许是int(假如是时刻戳)

由于永久无法在文本列上优化运行MAX,以是提议行使

MAX(m.message_id) AS latest_reply

相反,作为动静也许以日期次序插入,应该是等效的.

编辑添加：
查询所有写完……

$query = "
SELECT t.thread_id,title,MAX(m.message_id) AS latest_reply 
FROM forum_threads AS t
    LEFT JOIN forum_messages AS m ON t.thread_id = m.thread_id
WHERE t.child_id = ".$board_id."
GROUP BY t.thread_id
ORDER BY latest_reply DESC 
LIMIT ".$starting.",".$this->user['results_per_page'];

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!