summaryrefslogtreecommitdiff
path: root/lib/VNWeb
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2021-01-13 14:16:16 +0100
committerYorhel <git@yorhel.nl>2021-01-13 14:16:18 +0100
commit3681cc0dc55c655776005d4e13e8510c8228534e (patch)
tree91baf2570b4d151eaecb1487321fc4e943b5683b /lib/VNWeb
parent77d17854e5558d65625d8701f6e6c384d492047a (diff)
AdvSearch: Seiyuu->VA, "Staff" filter now includes VAs, remove role filter from Char->VA
I considered adding a separate "Voice actor" subquery for VNs, but the current system doesn't properly handle multiple different subqueries of the same type (i.e. two types of V->S subqueries). And it does make sense to consider VAs as part of the VNs staff, even more so when the staff role filter includes "Voice Actor" role. Fortunately, Postgres is pretty clever with optimizing these. If you specify VA as role, the vn_staff table isn't even scanned. Likewise, if you specify a non-VA role, the vn_seiyuu table is completely ignored. Unfortunately, these optimizations don't work when the role filter is inverted, but I /might/ be able to fix that too. Let's just see how this is received.
Diffstat (limited to 'lib/VNWeb')
-rw-r--r--lib/VNWeb/AdvSearch.pm11
1 files changed, 9 insertions, 2 deletions
diff --git a/lib/VNWeb/AdvSearch.pm b/lib/VNWeb/AdvSearch.pm
index cc4ea6a2..485bb73a 100644
--- a/lib/VNWeb/AdvSearch.pm
+++ b/lib/VNWeb/AdvSearch.pm
@@ -339,7 +339,13 @@ f v => 13 => 'anime-id', { id => 1 },
f v => 50 => 'release', 'r', '=' => sub { sql 'v.id IN(SELECT rv.vid FROM releases r JOIN releases_vn rv ON rv.id = r.id WHERE NOT r.hidden AND', $_, ')' };
f v => 51 => 'character','c', '=' => sub { sql 'v.id IN(SELECT cv.vid FROM chars c JOIN chars_vns cv ON cv.id = c.id WHERE NOT c.hidden AND', $_, ')' }; # TODO: Spoiler setting?
-f v => 52 => 'staff', 's', '=' => sub { sql 'v.id IN(SELECT vs.id FROM vn_staff vs JOIN staff_alias sa ON sa.aid = vs.aid JOIN staff s ON s.id = sa.id WHERE NOT s.hidden AND', $_, ')' };
+f v => 52 => 'staff', 's', '=' => sub {
+ # The "Staff" filter includes both vn_staff and vn_seiyuu. Union those tables together and filter on that.
+ sql 'v.id IN(SELECT vs.id
+ FROM (SELECT id, aid, role FROM vn_staff UNION ALL SELECT id, aid, NULL FROM vn_seiyuu) vs
+ JOIN staff_alias sa ON sa.aid = vs.aid
+ JOIN staff s ON s.id = sa.id
+ WHERE NOT s.hidden AND', $_, ')' };
@@ -420,8 +426,9 @@ f s => 5 => 'role', { enum => [ 'seiyuu', keys %CREDIT_TYPE ] },
my($neg, $all, $val) = @_;
my @grp = $all && @$val > 1 ? ('GROUP BY vs.aid HAVING COUNT(vs.role) =', \scalar @$val) : ();
if($#TYPE && $TYPE[$#TYPE-1] eq 'v') {
+ # Shortcut referencing the vn_staff table we're already querying
+ return $val->[0] eq 'seiyuu' ? 'vs.role IS NULL' : sql 'vs.role IN', $val if !@grp && !$neg;
return sql $neg ? 'NOT' : '', 'EXISTS(SELECT 1 FROM vn_seiyuu vs WHERE vs.id = v.id AND vs.aid = sa.aid)' if $val->[0] eq 'seiyuu';
- return sql 'vs.role IN', $val if !@grp && !$neg; # Shortcut referencing the vn_staff table we're already querying
sql 'sa.aid', $neg ? 'NOT' : '', 'IN(SELECT vs.aid FROM vn_staff vs WHERE vs.id = v.id AND vs.role IN', $val, @grp, ')';
} else {
return sql $neg ? 'NOT' : '', 'EXISTS(SELECT 1 FROM vn_seiyuu vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden AND vs.aid = sa.aid)' if $val->[0] eq 'seiyuu';