summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2011-01-01 18:09:00 +0100
committerYorhel <git@yorhel.nl>2011-01-01 18:09:00 +0100
commitb932e475e9ed2b0b1e89a5954fc1872a131890e0 (patch)
tree17439247f56f8700bfd8aa734baded2c62f4c09a
parent674cab796b2607c1e4441d0005d96588cedbde29 (diff)
Also apply permanent VN filters to the random screenshots on /
-rw-r--r--lib/VNDB/DB/VN.pm25
-rw-r--r--lib/VNDB/Handler/Misc.pm8
2 files changed, 29 insertions, 4 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index b01d3328..2a7f2477 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -11,7 +11,7 @@ our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreen
# Options: id, rev, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil,
-# hasani, results, page, what, sort, reverse
+# hasani, hasshot, results, page, what, sort, reverse
# What: extended anime relations screenshots relgraph rating ranking changes
# Sort: id rel pop rating title tagscore rand
sub dbVNGet {
@@ -46,6 +46,8 @@ sub dbVNGet {
'('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", ref $o{plat} ? @{$o{plat}} : $o{plat}).')' => 1 ) : (),
defined $o{hasani} ? (
'!sEXISTS(SELECT 1 FROM vn_anime va WHERE va.vid = vr.id)' => [ $o{hasani} ? '' : 'NOT ' ]) : (),
+ defined $o{hasshot} ? (
+ '!sEXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.vid = vr.id)' => [ $o{hasshot} ? '' : 'NOT ' ]) : (),
$o{tag_inc} ? (
'v.id IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)',
[ ref $o{tag_inc} ? $o{tag_inc} : [$o{tag_inc}], $o{tagspoil}, ref $o{tag_inc} ? $#{$o{tag_inc}}+1 : 1 ]) : (),
@@ -222,8 +224,10 @@ sub dbScreenshotGet {
# Fetch random VN + screenshots
+# if any arguments are given, it will return one random screenshot for each VN
sub dbScreenshotRandom {
- return shift->dbAll(q|
+ my($self, @vids) = @_;
+ return $self->dbAll(q|
SELECT s.id AS scr, s.width, s.height, vr.vid, vr.title
FROM screenshots s
JOIN vn_screenshots vs ON vs.scr = s.id
@@ -236,7 +240,22 @@ sub dbScreenshotRandom {
LIMIT 20
)
LIMIT 4|
- );
+ ) if !@vids;
+ # this query is faster than it looks
+ return $self->dbAll(join(' UNION ALL ', map
+ q|SELECT s.id AS scr, s.width, s.height, vr.vid, vr.title, RANDOM() AS position
+ FROM vn v
+ JOIN vn_rev vr ON vr.id = v.latest
+ JOIN vn_screenshots vs ON vs.vid = v.latest
+ JOIN screenshots s ON s.id = vs.scr
+ WHERE v.id = ? AND s.id = (
+ SELECT vs2.scr
+ FROM vn_screenshots vs2
+ JOIN vn v2 ON v2.latest = vs2.vid
+ WHERE v2.id = v.id
+ ORDER BY RANDOM()
+ LIMIT 1
+ )|, @vids).' ORDER BY position', @vids);
}
diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm
index 671cc017..90a457aa 100644
--- a/lib/VNDB/Handler/Misc.pm
+++ b/lib/VNDB/Handler/Misc.pm
@@ -45,7 +45,13 @@ sub homepage {
lit mt '_home_intro';
end;
- my $scr = $self->dbScreenshotRandom;
+ # with filters applied it's signifcantly slower, so special-code the situations with and without filters
+ my @vns;
+ if($self->authPref('filter_vn')) {
+ my $r = $self->filFetchDB(vn => undef, undef, {hasshot => 1, results => 4, order => 'rand'});
+ @vns = map $_->{id}, @$r;
+ }
+ my $scr = $self->dbScreenshotRandom(@vns);
p class => 'screenshots';
for (@$scr) {
my($w, $h) = imgsize($_->{width}, $_->{height}, @{$self->{scr_size}});