summaryrefslogtreecommitdiff
path: root/lib/VNDB
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-14 16:37:25 +0100
committerYorhel <git@yorhel.nl>2009-11-14 16:43:48 +0100
commited15643975e6e05ce5e8b9509901f769c86e06b4 (patch)
tree9cc857ca8217ce345d9b796217cd2858781c15ad /lib/VNDB
parentc7ff7086534cd9de7ce4043eb10c7161401c1b96 (diff)
Added bayesian rating and vote count to the VN list
It's even realtime! To my surprise this calculation isn't very heavy, or PostgreSQL is just extremely fast. The GetVN query on /v/all takes 100ms in the worst case (instead of the usual 30-60ms). Can always cache this later on.
Diffstat (limited to 'lib/VNDB')
-rw-r--r--lib/VNDB/DB/VN.pm5
-rw-r--r--lib/VNDB/Handler/VNBrowse.pm8
2 files changed, 11 insertions, 2 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index bb2c1275..b1fb103b 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -78,6 +78,8 @@ sub dbVNGet {
'JOIN users u ON u.id = c.requester' : (),
$o{what} =~ /relgraph/ ?
'JOIN relgraphs vg ON vg.id = v.rgraph' : (),
+ $o{what} =~ /rating/ ?
+ 'LEFT JOIN vn_ratings r ON r.vid = v.id' : (),
);
my $tag_ids = $o{tags_include} && join ',', @{$o{tags_include}[1]};
@@ -89,6 +91,7 @@ sub dbVNGet {
qw|c.requester c.comments v.latest u.username c.rev c.causedby|, q|extract('epoch' from c.added) as added|) : (),
$o{what} =~ /relgraph/ ? 'vg.svg' : (),
$o{what} =~ /ranking/ ? '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS ranking' : (),
+ $o{what} =~ /rating/ ? 'r.rating, r.votecount' : (),
$tag_ids ?
qq|(SELECT AVG(tvb.rating) FROM tags_vn_bayesian tvb WHERE tvb.tag IN($tag_ids) AND tvb.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvb.vid) AS tagscore| : (),
);
@@ -98,7 +101,7 @@ sub dbVNGet {
FROM vn_rev vr
!s
!W
- ORDER BY !s|,
+ ORDER BY !s NULLS LAST|,
join(', ', @select), join(' ', @join), \%where, $o{order},
);
diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm
index bca21151..87f3371a 100644
--- a/lib/VNDB/Handler/VNBrowse.pm
+++ b/lib/VNDB/Handler/VNBrowse.pm
@@ -16,7 +16,7 @@ sub list {
my($self, $char) = @_;
my $f = $self->formValidate(
- { name => 's', required => 0, default => 'tagscore', enum => [ qw|title rel pop tagscore| ] },
+ { name => 's', required => 0, default => 'tagscore', enum => [ qw|title rel pop tagscore rating| ] },
{ name => 'o', required => 0, enum => [ 'a','d' ] },
{ name => 'p', required => 0, default => 1, template => 'int' },
{ name => 'q', required => 0, default => '' },
@@ -56,6 +56,7 @@ sub list {
$f->{o} = $f->{s} eq 'tagscore' ? 'd' : 'a' if !$f->{o};
my($list, $np) = $self->dbVNGet(
+ what => 'rating',
$char ne 'all' ? ( char => $char ) : (),
$f->{q} ? ( search => $f->{q} ) : (),
results => 50,
@@ -90,6 +91,7 @@ sub list {
[ '', 0, undef, 'tc3' ],
[ mt('_vnbrowse_col_released'), 'rel', undef, 'tc4' ],
[ mt('_vnbrowse_col_popularity'), 'pop', undef, 'tc5' ],
+ [ mt('_vnbrowse_col_rating'), 'rating', undef, 'tc6' ],
],
row => sub {
my($s, $n, $l) = @_;
@@ -114,6 +116,10 @@ sub list {
lit $self->{l10n}->datestr($l->{c_released});
end;
td class => 'tc5', sprintf '%.2f', $l->{c_popularity}*100;
+ td class => 'tc6';
+ txt sprintf '%.2f', $l->{rating}||0;
+ b class => 'grayedout', sprintf ' (%d)', $l->{votecount}||0;
+ end;
end;
},
);