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.

7 files changed, 40 insertions, 3 deletions

diff --git a/ChangeLog b/ChangeLog
index 98d46ab0..92cfa0e8 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -9,6 +9,7 @@ git - ?
 	- Allow NULL values for releases_rev.minage
 	- Made age ratings and external VN link titles translatable
 	- Added wikipedia link for producers
+	- Added bayesian rating for VNs
 
 2.8 - 2009-10-24
 	- Converted relation graphs to use inline SVG
diff --git a/data/lang.txt b/data/lang.txt
index e0e7bfce..68813be9 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -4627,6 +4627,12 @@ ru : Популярность
 cs : Popularita
 hu : Népszerűség
 
+:_vnbrowse_col_rating
+en : Rating
+ru*:
+cs*:
+hu*:
+
 :_vnbrowse_tagign_title
 en : The following tags were ignored:
 ru : Следующие теги были пропущены:
diff --git a/data/style.css b/data/style.css
index 7653824e..d6542477 100644
--- a/data/style.css
+++ b/data/style.css
@@ -786,7 +786,8 @@ div.scr_uploader { visibility: hidden; overflow: hidden; width: 1px; height: 1px
 .vnbrowse .tc_s { width: 65px }
 .vnbrowse .tc2 { text-align: right; padding: 0; }
 .vnbrowse .tc3 { padding: 0; }
-.vnbrowse .tc5 { text-align: right; padding-right: 10px; }
+.vnbrowse .tc5 { text-align: right; padding-right: 10px }
+.vnbrowse .tc6 { width: 80px }
 #advselect {
   text-align: center;
   display: block;
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;
     },
   );
diff --git a/util/dump.sql b/util/dump.sql
index dfdeb021..d64fe12f 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -829,6 +829,16 @@ CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PRO
 ---------------------------------
 
 
+-- bayesian rating view
+CREATE OR REPLACE VIEW vn_ratings AS
+  SELECT vid, COUNT(uid) AS votecount, (
+      ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) /
+      ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
+    ) AS rating
+  FROM votes
+  GROUP BY vid;
+
+
 -- Sequences used for ID generation of items not in the DB
 CREATE SEQUENCE covers_seq;
 
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
index 9bb00979..68ea2052 100644
--- a/util/updates/update_2.9.sql
+++ b/util/updates/update_2.9.sql
@@ -42,3 +42,13 @@ UPDATE releases_rev SET minage = NULL WHERE minage < 0;
 -- wikipedia link for producers
 ALTER TABLE producers_rev ADD COLUMN l_wp varchar(150);
 
+
+-- bayesian rating
+CREATE OR REPLACE VIEW vn_ratings AS
+  SELECT vid, COUNT(uid) AS votecount, (
+      ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) /
+      ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
+    ) AS rating
+  FROM votes
+  GROUP BY vid;
+