Fetching the last post of the forum

Question

I build a forum system and I have a problem with fetching the last post.

Here is the query:

SELECT 
    posts.date, posts.author AS pauthor, topics.*,
    CASE 
       WHEN posts.date > topics.date THEN posts.date 
       WHEN topics.date > posts.date THEN topics.date
    END AS ldate
FROM 
    posts, topics 
WHERE 
    topics.id = posts.topic_id 
    AND forum_id = ? 
ORDER BY 
    ldate DESC 
LIMIT 1

The problem is when I open a new topic is not appear unless I post a comment on this topic.

I don't understand: if you want the last post, empty topics should not matter, should they? — didierc, Jun 24 '14 at 14:13
So maybe the problem is in the script which show the topic, not with MySQL query? — Tikky, Jun 24 '14 at 14:17
@didierc I wont to show the last column on each forum Whether is last comment or the newset topic. — user3553937, Jun 24 '14 at 14:24

Gordon Linoff · Answer 1 · 2014-06-24T17:19:30.640

1

Here is the query rewritten with proper join syntax and table aliases:

SELECT p.date, p.author AS pauthor, t.*,
       (CASE WHEN p.date > t.date THEN p.date 
             WHEN t.date > p.date THEN t.date
        END) AS ldate
FROM posts p JOIN
     topics t
     ON t.id = p.topic_id
WHERE forum_id = ?
ORDER BY ldate DESC
LIMIT 1;

You need a left outer join and a bit more logic:

SELECT p.date, p.author AS pauthor, t.*,
       (CASE WHEN t.date IS NULL THEN p.date
             WHEN p.date > t.date THEN p.date 
             WHEN t.date > p.date THEN t.date
        END) AS ldate
FROM topics t LEFT JOIN
     posts p 
     ON t.id = p.topic_id
WHERE forum_id = ?
ORDER BY ldate DESC
LIMIT 1;

edited Jun 24 '14 at 17:19

answered Jun 24 '14 at 14:32

Gordon Linoff

1,242,037
58
646
786

I was guessing that perhaps an left join would do the trick, but I don't understand: how would it turn into an empty resultset when a topic is empty? – didierc Jun 24 '14 at 14:52
@didierc . . . I don't understand your question. This should create an empty result set. – Gordon Linoff Jun 24 '14 at 15:06
Sorry, I meant, does it produce a null result when the last created element is a topic rather than a post? (It seems to be what OP wants, but I cannot be certain). – didierc Jun 24 '14 at 15:12
@didierc . . . The first two columns relate to the post and would not be `NULL`. The columns related to the topic would be `NULL`. That seems to be the intent of the question. – Gordon Linoff Jun 24 '14 at 15:14
I don't think that's what OP meant, I think he actually wants a right join, but otherwise your query is of course flawless. – didierc Jun 24 '14 at 15:32
@user3553937 . . . The joins were in the wrong order. – Gordon Linoff Jun 24 '14 at 17:19

score 0 · Answer 2 · edited May 23 '17 at 10:28

0

I'm going to assume that the post if in the database but is not displayed on the webpage where you want it. Maybe the issue is that ORDER BY puts nulls on the bottom so

ORDER BY ldate  IS NULL DESC, ldate  DESC

edited May 23 '17 at 10:28

Community

1
1

answered Jun 24 '14 at 14:19

Useless Intern

1,294
1
10
20

Fetching the last post of the forum

2 Answers2