diff options
author | Yorhel <git@yorhel.nl> | 2021-01-13 14:16:16 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2021-01-13 14:16:18 +0100 |
commit | 3681cc0dc55c655776005d4e13e8510c8228534e (patch) | |
tree | 91baf2570b4d151eaecb1487321fc4e943b5683b /lib/VNWeb | |
parent | 77d17854e5558d65625d8701f6e6c384d492047a (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.pm | 11 |
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'; |