From 2d7464077ae805a1923bb4e2024afeb769f30e02 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Tue, 9 Jan 2007 10:36:33 +0000 Subject: [PATCH] unread query outside include --- ND.pm | 12 +++--------- ND/Include.pm | 13 ++++++++++++- 2 files changed, 15 insertions(+), 10 deletions(-) diff --git a/ND.pm b/ND.pm index 3c053b8..c3391f2 100755 --- a/ND.pm +++ b/ND.pm @@ -26,6 +26,7 @@ use DBD::Pg qw(:pg_types); use Apache2::Request; use ND::Web::Include; use ND::DB; +use ND::Include; use Tie::File; use Fcntl 'O_RDONLY'; use strict; @@ -110,15 +111,8 @@ sub page { $fleetupdate = 0 unless defined $fleetupdate; - my ($unread) = $DBH->selectrow_array(q{ - SELECT count(*) AS unread -FROM forum_boards fb NATURAL JOIN forum_threads ft - JOIN forum_posts fp USING (ftid) LEFT OUTER JOIN - (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid -WHERE (ftv.time IS NULL OR fp.time > ftv.time) AND fbid > 0 AND - fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1))) - },undef,$UID) or $ERROR .= p($DBH->errstr); - + my ($unread,$lastv) = $DBH->selectrow_array(unread_query,undef,$UID) or $ERROR .= p($DBH->errstr); + $TEMPLATE->param(UnreadPosts => $unread); $TEMPLATE->param(Tick => $TICK); $TEMPLATE->param(isMember => (($TICK - $fleetupdate < 24) || isScanner()) && $PLANET && isMember()); diff --git a/ND/Include.pm b/ND/Include.pm index 87d13d0..9bfb05e 100644 --- a/ND/Include.pm +++ b/ND/Include.pm @@ -25,7 +25,7 @@ require Exporter; our @ISA = qw/Exporter/; -our @EXPORT = qw/min max log_message intel_log/; +our @EXPORT = qw/min max log_message intel_log unread_query/; sub min { my ($x,$y) = @_; @@ -51,4 +51,15 @@ sub intel_log { $log->execute($uid,$message,$planet) or $ND::ERROR .= p($ND::DBH->errstr); } +sub unread_query { + return $ND::DBH->prepare_cached(q{ + SELECT count(*) AS unread, max(ftv.time) AS last_vist +FROM forum_boards fb NATURAL JOIN forum_threads ft + JOIN forum_posts fp USING (ftid) LEFT OUTER JOIN + (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid +WHERE (ftv.time IS NULL OR fp.time > ftv.time) AND fbid > 0 AND + fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1))) + }); +} + 1; -- 2.39.2