summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/VN.pm
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-14 18:46:59 +0100
committerYorhel <git@yorhel.nl>2009-11-14 18:46:59 +0100
commit151a8338c931389c6d67389c56c2d8862e882de6 (patch)
tree3f5c8c691f92005324c5c2cd965a4a0d04c4fcd1 /lib/VNDB/DB/VN.pm
parent2394a77e8d503ebcf78109ced76680e46770b617 (diff)
SQL: Cached bayesian VN rating and vote counts
Was a good idea after all...
Diffstat (limited to 'lib/VNDB/DB/VN.pm')
-rw-r--r--lib/VNDB/DB/VN.pm10
1 files changed, 4 insertions, 6 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 4201181a..250f5267 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -11,7 +11,7 @@ our @EXPORT = qw|dbVNGet dbVNAdd dbVNEdit dbVNImageId dbVNCache dbScreenshotAdd
# Options: id, rev, char, search, lang, platform, tags_include, tags_exclude, results, page, order, what
-# What: extended anime relations screenshots relgraph ranking changes
+# What: extended anime relations screenshots relgraph rating ranking changes
sub dbVNGet {
my($self, %o) = @_;
$o{results} ||= 10;
@@ -78,23 +78,21 @@ sub dbVNGet {
'JOIN users u ON u.id = c.requester' : (),
$o{what} =~ /relgraph/ ?
'JOIN relgraphs vg ON vg.id = v.rgraph' : (),
- $o{what} =~ /(rating|ranking)/ ?
- 'LEFT JOIN vn_ratings r ON r.vid = v.id' : (),
);
my $tag_ids = $o{tags_include} && join ',', @{$o{tags_include}[1]};
my @select = (
- qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph v.c_popularity|, 'vr.id AS cid',
+ qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph|, 'vr.id AS cid',
$o{what} =~ /extended/ ? (
qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (),
$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} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (),
$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',
+ '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > v.c_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| : (),
);