Added bayesian rating info to VN pages

This adds about 100ms (sometimes more) to the page generation time of VN
pages... maybe I should cache the ratings after all.

3 files changed, 9 insertions, 5 deletions

diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index b1fb103b..4201181a 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -78,7 +78,7 @@ sub dbVNGet {
       'JOIN users u ON u.id = c.requester' : (),
     $o{what} =~ /relgraph/ ?
       'JOIN relgraphs vg ON vg.id = v.rgraph' : (),
-    $o{what} =~ /rating/ ?
+    $o{what} =~ /(rating|ranking)/ ?
       'LEFT JOIN vn_ratings r ON r.vid = v.id' : (),
   );
 
@@ -90,7 +90,10 @@ sub dbVNGet {
     $o{what} =~ /changes/ ? (
       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} =~ /ranking/ ? (
+      '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS p_ranking',
+      '(SELECT COUNT(*)+1 FROM vn_ratings ir WHERE ir.rating > r.rating) AS r_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| : (),
diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm
index b27fa58b..2a333337 100644
--- a/lib/VNDB/Handler/VNPage.pm
+++ b/lib/VNDB/Handler/VNPage.pm
@@ -46,7 +46,7 @@ sub page {
 
   my $v = $self->dbVNGet(
     id => $vid,
-    what => 'extended anime relations screenshots ranking'.($rev ? ' changes' : ''),
+    what => 'extended anime relations screenshots rating ranking'.($rev ? ' changes' : ''),
     $rev ? (rev => $rev) : (),
   )->[0];
   return 404 if !$v->{id};
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index ad99d32d..857cdcef 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -481,8 +481,9 @@ sub htmlVoteStats {
    clearfloat;
    if($type eq 'v') {
      div;
-      h3 mt '_votestats_pop_title';
-      p mt '_votestats_pop_sum', $obj->{ranking}, $self->{stats}{vn}, sprintf('%0.2f',$obj->{c_popularity}*100);
+      h3 mt '_votestats_rank_title';
+      p mt '_votestats_rank_pop', $obj->{p_ranking}, sprintf '%.2f', $obj->{c_popularity}*100;
+      p mt '_votestats_rank_rat', $obj->{r_ranking}, sprintf '%.2f', $obj->{rating};
      end;
    }
   end;