diff options
author | Yorhel <git@yorhel.nl> | 2009-11-14 18:46:59 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-11-14 18:46:59 +0100 |
commit | 151a8338c931389c6d67389c56c2d8862e882de6 (patch) | |
tree | 3f5c8c691f92005324c5c2cd965a4a0d04c4fcd1 /lib/VNDB/DB/VN.pm | |
parent | 2394a77e8d503ebcf78109ced76680e46770b617 (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.pm | 10 |
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| : (), ); |