AdvSearch: Add staff role filter + fix bug with some OR queries

When the role filter is used as part of a Seiyuu subquery, it will match
the roles that the person has performed in any VN, not necessarily the
VNs where the character has been voiced. This is not ideal, I think, but
doing this any other way is likely going to kill performance.

The role filter when used as part of a VN->Staff subquery will match the
VN, as anything else would be fairly useless. This also has a few cases
where performance is impossibly slow and will timeout, but most queries
seem to work alright.

3 files changed, 55 insertions, 2 deletions

diff --git a/elm/AdvSearch/Fields.elm b/elm/AdvSearch/Fields.elm
index ac8b40ba..54e27cf4 100644
--- a/elm/AdvSearch/Fields.elm
+++ b/elm/AdvSearch/Fields.elm
@@ -262,6 +262,7 @@ type FieldModel
   | FMAniStory   (AS.Model Int)
   | FMRType      (AS.Model String)
   | FMLabel      (AS.Model Int)
+  | FMSRole      (AS.Model String)
   | FMHeight     (AR.Model Int)
   | FMWeight     (AR.Model Int)
   | FMBust       (AR.Model Int)
@@ -301,6 +302,7 @@ type FieldMsg
   | FSAniStory   (AS.Msg Int)
   | FSRType      (AS.Msg String)
   | FSLabel      (AS.Msg Int)
+  | FSSRole      (AS.Msg String)
   | FSHeight     AR.Msg
   | FSWeight     AR.Msg
   | FSBust       AR.Msg
@@ -422,6 +424,7 @@ fields =
   , f S "Name"               0  FMStaff       AT.init                 AT.fromQuery
   , f S "Language"           1  FMLang        AS.init                 AS.langFromQuery
   , f S "Gender"             2  FMGender      AS.init                 AS.genderFromQuery
+  , f S "Role"               3  FMSRole       AS.init                 AS.sroleFromQuery
   ]
 
 
@@ -479,6 +482,7 @@ fieldUpdate dat msg_ (num, dd, model) =
       (FSAniStory msg, FMAniStory m) -> maps FMAniStory (AS.update msg m)
       (FSRType  msg,   FMRType m)    -> maps FMRType    (AS.update msg m)
       (FSLabel  msg,   FMLabel m)    -> maps FMLabel    (AS.update msg m)
+      (FSSRole  msg,   FMSRole m)    -> maps FMSRole    (AS.update msg m)
       (FSHeight msg,   FMHeight m)   -> maps FMHeight   (AR.update msg m)
       (FSWeight msg,   FMWeight m)   -> maps FMWeight   (AR.update msg m)
       (FSBust msg,     FMBust m)     -> maps FMBust     (AR.update msg m)
@@ -543,6 +547,7 @@ fieldView dat (_, dd, model) =
       FMAniStory m   -> f FSAniStory   (AS.animatedView True m)
       FMRType m      -> f FSRType      (AS.rtypeView m)
       FMLabel m      -> f FSLabel      (AS.labelView dat m)
+      FMSRole m      -> f FSSRole      (AS.sroleView m)
       FMHeight m     -> f FSHeight     (AR.heightView m)
       FMWeight m     -> f FSWeight     (AR.weightView m)
       FMBust m       -> f FSBust       (AR.bustView m)
@@ -586,6 +591,7 @@ fieldToQuery dat (_, _, model) =
     FMAniStory m -> AS.toQuery (QInt 14) m
     FMRType m    -> AS.toQuery (QStr 16) m
     FMLabel m    -> AS.toQuery (\op v -> QTuple 12 op (Maybe.withDefault 0 dat.uid) v) m
+    FMSRole m    -> AS.toQuery (QStr 5) m
     FMHeight m   -> AR.toQuery (QInt 6) (QStr 6) m
     FMWeight m   -> AR.toQuery (QInt 7) (QStr 7) m
     FMBust m     -> AR.toQuery (QInt 8) (QStr 8) m
diff --git a/elm/AdvSearch/Set.elm b/elm/AdvSearch/Set.elm
index e44574f4..80d66089 100644
--- a/elm/AdvSearch/Set.elm
+++ b/elm/AdvSearch/Set.elm
@@ -413,3 +413,28 @@ labelFromQuery dat q =
     case qs of
       QTuple 12 op uid l -> if Just uid == dat.uid then Just (op, l) else Nothing
       _ -> Nothing) dat q
+
+
+
+
+-- Staff role
+
+sroleView model =
+  let lst = ("seiyuu","Voice actor") :: GT.creditTypes
+  in
+  ( case Set.toList model.sel of
+      []  -> b [ class "grayedout" ] [ text "Role" ]
+      [v] -> span [ class "nowrap" ] [ lblPrefix model, text <| Maybe.withDefault "" <| lookup v lst ]
+      l   -> span [ class "nowrap" ] [ lblPrefix model, text <| "Roles (" ++ String.fromInt (List.length l) ++ ")" ]
+  , \() ->
+    [ div [ class "advheader" ]
+      [ h3 [] [ text "Role" ]
+      , opts model True True ]
+    , ul [] <| List.map (\(k,l) -> li [] [ linkRadio (Set.member k model.sel) (Sel k) [ text l ] ]) lst
+    ]
+  )
+
+sroleFromQuery = fromQuery (\q ->
+  case q of
+    QStr 5 op v -> Just (op, v)
+    _ -> Nothing)
diff --git a/lib/VNWeb/AdvSearch.pm b/lib/VNWeb/AdvSearch.pm
index 994dd165..3b372c42 100644
--- a/lib/VNWeb/AdvSearch.pm
+++ b/lib/VNWeb/AdvSearch.pm
@@ -299,6 +299,7 @@ sub f {
     $NUMFIELDS{$t}{$num} = $n;
 }
 
+my @TYPE; # stack of query types, $TYPE[0] is the top-level query, $TYPE[$#TYPE] the query currently being processed.
 
 
 f v =>  2 => 'lang',      { enum => \%LANGUAGE }, '=' => sub { sql 'v.c_languages && ARRAY', \$_, '::language[]' };
@@ -398,9 +399,24 @@ f c => 52 => 'seiyuu',    's', '=' => sub { sql 'c.id IN(SELECT vs.cid FROM vn_s
 
 
 
+# Staff filters need both 'staff s' and 'staff_alias sa' - aliases are treated as separate rows.
 f s =>  2 => 'lang',      { enum => \%LANGUAGE }, '=' => sub { sql 's.lang =', \$_ };
 f s =>  3 => 'id',        { vndbid => 's' }, '=' => sub { sql 's.id = vndbid_num(', \$_, ')' };
 f s =>  4 => 'gender',    { enum => \%GENDER }, '=' => sub { sql 's.gender =', \$_ };
+f s =>  5 => 'role',      { enum => [ 'seiyuu', keys %CREDIT_TYPE ] },
+    sql_list_grp => sub { $_ eq 'seiyuu' ? undef : '' },
+    sql_list => sub {
+        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') {
+            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';
+            sql 'sa.aid', $neg ? 'NOT' : '', 'IN(SELECT vs.aid FROM vn_staff vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden AND vs.role IN', $val, @grp, ')';
+        }
+    };
 
 
 
@@ -602,13 +618,19 @@ sub _sql_where {
 
     my $f = $FIELDS{$t}{$q->[0]};
     my $func = $f->{$q->[1]} || $f->{sql};
-    local $_ = ref $f->{value} ? $q->[2] : _sql_where($f->{value}, $q->[2]);
-    $func->($q->[1]);
+    local $_ = ref $f->{value} ? $q->[2] : do {
+        push @TYPE, $f->{value};
+        my $v = _sql_where($f->{value}, $q->[2]);
+        pop @TYPE;
+        $v;
+    };
+    sql '(', $func->($q->[1]), ')';
 }
 
 
 sub sql_where {
     my($self) = @_;
+    @TYPE = ($self->{type});
     $self->{query} ? _sql_where $self->{type}, _canon $self->{type}, $self->{query} : '1=1';
 }