我在为此编写查询时遇到了一些麻烦.我想我有基本的逻辑,虽然我可能没有.我想要做的是根据用户正在查看的主板获取所有线程,然后根据上次回复的时间对这些线程进行排序.查询不返回任何错误,它只获取最近更新的线程.
这是我的查询：
$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'];