my $dbh = $c->model;
my $posts = $dbh->prepare(q{
- SELECT u.uid,u.username,date_trunc('seconds',fp.time::timestamp) AS time
+ SELECT fpid,u.uid,u.username,date_trunc('seconds',fp.time::timestamp) AS time
,fp.message,COALESCE(fp.time > ftv.time,TRUE) AS unread
FROM forum_threads ft
JOIN forum_posts fp USING (ftid)
}
}
+sub markThreadAsUnread : Local {
+ my ( $self, $c, $thread ) = @_;
+ my $dbh = $c->model;
+
+ my ($fbid) = $dbh->selectrow_array(q{
+SELECT fbid FROM forum_threads WHERE ftid = $1
+ },undef, $thread);
+
+ $dbh->do(q{
+DELETE FROM forum_thread_visits WHERE uid = $1 AND ftid = $2
+ }, undef, $c->user->id, $thread);
+ $c->res->redirect($c->uri_for('board',$fbid));
+}
+
+sub markPostAsUnread : Local {
+ my ( $self, $c, $post ) = @_;
+ my $dbh = $c->model;
+
+ my ($fbid) = $dbh->selectrow_array(q{
+SELECT fbid FROM forum_threads JOIN forum_posts USING (ftid) WHERE fpid = $1
+ },undef, $post);
+
+ $dbh->do(q{
+UPDATE forum_thread_visits ftv SET time = (fp.time - interval '1 second')
+FROM forum_posts fp
+WHERE ftv.uid = $1 AND fp.fpid = $2 AND fp.ftid = ftv.ftid
+ }, undef, $c->user->id, $post);
+ $c->res->redirect($c->uri_for('board',$fbid));
+}
+
sub moveThreads : Local {
my ( $self, $c, $board ) = @_;
my $dbh = $c->model;
my $query = $dbh->prepare(q{SELECT uid,username FROM users
WHERE uid > 0 AND uid IN (SELECT uid FROM groupmembers)
- ORDER BY LOWER(username)});
+ ORDER BY username});
$query->execute;
$c->stash(users => $query->fetchall_arrayref({}) );
FROM forum_boards fb
NATURAL JOIN forum_threads ft
NATURAL JOIN forum_categories fc
- LEFT OUTER JOIN (SELECT * FROM forum_access
- WHERE gid IN (SELECT groups($2))
+ LEFT OUTER JOIN (SELECT fa.* FROM forum_access fa
+ JOIN (SELECT groups($2) AS gid) g USING (gid)
) fa USING (fbid)
WHERE ft.ftid = $1 AND (fa.post IS NOT NULL
OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2))