CODEWITHSUNDEEP
mysqlsqlselectgroup-byleft-join
Christoffer
Christoffer

Reputation: 492

Select threads from forum based on comments

I've got the following sql that will return a list of forums. Under each forum it will select the thread with the latest comment. This works fine but when a new thread hasn't got any comments, nothing is returned.

How to tackle this problem?

SELECT  f.Id AS forum_id,
            f.name AS forum_name,
            f.slug AS forum_slug,
            f.image AS forum_image,
            t.Id AS thread_id,
            t.title AS thread_topic,
            t.unixtime AS thread_timestamp,
            p.Id AS post_id,
            p.content AS post_content,
            p.unixtime AS post_timestamp,
            (SELECT COUNT(*) FROM a_comments o WHERE o.forumID=f.Id AND o.teamId = {$teamId}) comments_count,
            (SELECT COUNT(*) FROM a_threads w WHERE w.forumID=f.Id AND w.teamId = {$teamId}) threads_count

    FROM   a_forums f
    LEFT JOIN (SELECT t2.forumID, max(COALESCE(p2.unixtime, t2.unixtime)) as ts, COUNT(p2.unixtime) as post_count
          FROM a_threads t2 
          LEFT JOIN a_comments p2 ON p2.threadId = t2.id
          GROUP BY t2.forumId) max_p ON f.id = max_p.forumId
    LEFT JOIN   a_comments p ON max_p.ts = p.unixtime AND p.teamId = {$teamId} AND p.deleted = 0
    LEFT JOIN   a_threads t ON f.Id = t.forumID AND (max_p.post_count = 0 OR p.threadId = t.ID) AND t.teamId = {$teamId} AND t.deleted = 0
    ORDER BY f.id

Upvotes: 1

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270361

I think you just have to change the LEFT JOIN in the first subquery to a JOIN. With the LEFT JOIN, you'll get NULL or a non-valid time for the comment. This then throws off the rest of the logic -- I think.

SELECT f.Id AS forum_id, f.name AS forum_name, f.slug AS forum_slug, f.image AS forum_image,
       t.Id AS thread_id, t.title AS thread_topic, t.unixtime AS thread_timestamp,
       p.Id AS post_id, p.content AS post_content, p.unixtime AS post_timestamp,
       (SELECT COUNT(*) FROM a_comments o WHERE o.forumID=f.Id AND o.teamId = {$teamId}) as comments_count,
       (SELECT COUNT(*) FROM a_threads w WHERE w.forumID=f.Id AND w.teamId = {$teamId}) as threads_count
FROM a_forums f LEFT JOIN 
     (SELECT t2.forumID, max(p2.unixtime) as ts,
             COUNT(p2.unixtime) as post_count
      FROM a_threads t2 JOIN
           a_comments p2
           ON p2.threadId = t2.id
      GROUP BY t2.forumId
     ) max_p
     ON f.id = max_p.forumId LEFT JOIN
     a_comments p
     ON max_p.ts = p.unixtime AND p.teamId = {$teamId} AND
        p.deleted = 0 LEFT JOIN
     a_threads t
     ON f.Id = t.forumID AND (max_p.post_count = 0 OR p.threadId = t.ID) AND t.teamId = {$teamId} AND t.deleted = 0
ORDER BY f.id

Upvotes: 2

Related Questions