CODEWITHSUNDEEP
phpsql
user3553937
user3553937

Reputation: 1

Fetching the last post of the forum

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.

Upvotes: 0

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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;

Upvotes: 1

Useless Intern
Useless Intern

Reputation: 1302

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

Upvotes: 0

Related Questions