- my $threads = $dbh->prepare(q{SELECT fcid,category,fbid,board,ft.ftid,u.username,ft.subject,
- count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts,
- date_trunc('seconds',max(fp.time)::timestamp) as last_post,
- min(fp.time)::date as posting_date, ft.sticky
- FROM forum_categories fc
- JOIN forum_boards fb USING (fcid)
- JOIN forum_threads ft USING (fbid)
- JOIN forum_posts fp USING (ftid)
- JOIN users u ON u.uid = ft.uid
- LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid
- WHERE (fbid > 0 AND
- fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1)))
- ) OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1)
- GROUP BY fcid,category,fbid,board,ft.ftid, ft.subject,ft.sticky,u.username
- HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
- ORDER BY fcid,fbid,sticky DESC,last_post DESC
+ my $threads = $dbh->prepare(q{
+SELECT fcid,category,fbid,board,ft.ftid,u.username,ft.subject,ft.sticky
+ ,(SELECT count(*) FROM forum_posts WHERE ftid = ft.ftid
+ AND COALESCE(time > ftv.time,TRUE)) AS unread
+ ,ft.posts,date_trunc('seconds',ft.mtime::timestamp) as last_post
+ ,ft.ctime::DATE as posting_date
+FROM forum_categories fc
+ JOIN forum_boards fb USING (fcid)
+ JOIN forum_threads ft USING (fbid)
+ JOIN users u ON u.uid = ft.uid
+ LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1)
+ ftv ON ftv.ftid = ft.ftid
+WHERE COALESCE(ft.mtime > ftv.time,TRUE)
+ AND ft.ftid IN (SELECT ftid FROM forum_posts WHERE ftid = ft.ftid)
+ AND ((fbid > 0 AND
+ fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1))))
+ OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1))
+ORDER BY fcid,fbid,sticky DESC,last_post DESC