|
Top Hotlisted Box
|
|
By panner , Section Code []
Posted on Tue Jul 31, 2001 at 12:00:00 PM PST
|
 |
|
Recently, on k5 there was a discussion
about disabling the Anonymous Hero. That story brought up several threads about
the advantages and disadvantags of doing so. This, however, has nothing to do
with that.
Instead, it relates to a specifc thread, in which ramses0 brought up his idea for
a top hotlisted box. Not wanting to let an opportunity for a perfectly good box
slip by, I managed to write one in about 15 minutes. The entire thing in (from
what I can tell) working form is below. Just goes to show the power of boxes in Scoop :)
|
my ($rv,$sth) = $S->db_select({
FROM => 'stories, viewed_stories',
WHAT => 'stories.sid, title, COUNT(viewed_stories.sid) AS total',
WHERE => 'viewed_stories.sid = stories.sid AND hotlisted = 1',
GROUP_BY => 'viewed_stories.sid',
ORDER_BY => 'total DESC',
LIMIT => '10'
});
my @data;
my $where;
while (my @s = $sth->fetchrow_array) {
push(@data, \@s);
$where .= ' OR ' if $where;
$where .= 'sid = ' . $S->{DBH}->quote($s[0]);
}
$sth->finish;
($rv,$sth) = $S->db_select({
FROM => 'comments',
WHAT => 'sid, COUNT(sid)',
WHERE => $where,
GROUP_BY => 'sid'
});
my %counts;
while (my $s = $sth->fetchrow_arrayref) {
$counts{ $s->[0] } = $s->[1];
my $c = $counts{ $s->[0] };
}
$sth->finish;
my $out;
foreach my $d (@data) {
my $up = ($d->[2] == 1) ? '' : 's';
my $c = $counts{$d->[0]} || 0;
my $cp = ($c == 1) ? '' : 's';
$out .= qq~%%dot%% <a href="%%rootdir%%/story/$d->[0]">$d->[1]</a> ($c comment$cp) by $d->[2] user$up<br>~;
}
return $out;
I've only tested it locally, but it works for me (famous last words :). It
could use some caching, which wouldn't be too difficult, but besides that
should be fine. The second select could be removed if comment counts were
stored with stories (as they will be soon), but for now that works.
Any problems/suggestions/comments, post below. If any changes are necessary,
I'll update this (though hopefully it won't become another never
ending story).
|
|
|
Story Views
|
|
32 Scoop users have viewed this story.
|
|
