Discussions::Elm: Improve board search a bit

Both in the quality of the results and query performance.
The performance boost wasn't as significant as I had hoped, but it's
something.

1 files changed, 20 insertions, 23 deletions

diff --git a/lib/VNWeb/Discussions/Elm.pm b/lib/VNWeb/Discussions/Elm.pm
index e4a1c992..fd2e4cd3 100644
--- a/lib/VNWeb/Discussions/Elm.pm
+++ b/lib/VNWeb/Discussions/Elm.pm
@@ -1,7 +1,6 @@
 package VNWeb::Discussions::Elm;
 
 use VNWeb::Prelude;
-use VNWeb::Discussions::Lib;
 
 # Autocompletion search results for boards
 elm_api Boards => undef, {
@@ -11,34 +10,32 @@ elm_api Boards => undef, {
     my $q = shift->{search};
     my $qs = sql_like $q;
 
-    my sub subq {
-        my($prio, $where) = @_;
-        sql 'SELECT', $prio, ' AS prio, btype, iid, CASE WHEN iid IS NULL THEN NULL ELSE title END AS title
-           FROM (',
-              sql_join('UNION ALL',
-                sql('SELECT btype, iid, title, original, hidden FROM', sql_boards(), 'a'),
-                map sql('SELECT', \$_, '::board_type, NULL,', \$BOARD_TYPE{$_}{txt}, q{, '', false}),
-                grep !$BOARD_TYPE{$_}{dbitem} && ($BOARD_TYPE{$_}{post_perm} eq 'board' || auth->permBoardmod),
-                keys %BOARD_TYPE
-              ),
-           ') x WHERE NOT x.hidden AND', $where
+    my sub item {
+        my($tbl, $type, $title, $filt, $query) = @_;
+        my $title_score = sql "1+substr_score(lower($title),", \$qs, ')';
+        sql 'SELECT',
+                $q =~ /^$type$RE{num}$/
+                ? sql 'CASE WHEN id =', \$q, 'THEN 0 ELSE', $title_score, 'END'
+                : $title_score,
+                ',', \$type, "::board_type, id, $title
+            FROM $tbl
+           WHERE", $filt, 'AND', sql_or(
+               $query, $q =~ /^$type$RE{num}$/ ? sql 'id =', \$q : ());
     }
 
-    # This query is SLOW :(
     elm_BoardResult tuwf->dbPagei({ results => 10, page => 1 },
         'SELECT btype, iid, title
            FROM (',
              sql_join('UNION ALL',
-                 # ID match
-                 $q =~ /^($BOARD_RE)$/ && $q =~ /^(([a-z]+)[0-9]*)$/
-                    ? subq(0, sql_and sql('btype =', \"$2"), $1 ne $2 ? sql('iid =', \"$1") : ()) : (),
-                 subq(
-                     sql('1+LEAST(substr_score(lower(title),', \$qs, '), substr_score(lower(original),', \$qs, '))'),
-                     sql('title ILIKE', \"%$qs%", ' OR original ILIKE', \"%$qs%")
-                 )
-             ), ') x
-           GROUP BY btype, iid, title
-           ORDER BY MIN(prio), btype, iid'
+                 (map sql('SELECT 1, ', \$_, '::board_type, NULL::vndbid, NULL'),#, \$BOARD_TYPE{$_}{txt}),
+                     grep $q eq $_ || $BOARD_TYPE{$_}{txt} =~ /\Q$q/i,
+                     grep !$BOARD_TYPE{$_}{dbitem} && ($BOARD_TYPE{$_}{post_perm} eq 'board' || auth->permBoardmod),
+                     keys %BOARD_TYPE),
+                 item('vn', 'v', 'title', 'NOT hidden', sql 'c_search LIKE ALL (search_query(', \$q, '))'),
+                 item('producers', 'p', 'name', 'NOT hidden', sql 'c_search LIKE ALL (search_query(', \$q, '))'),
+                 item('users', 'u', 'username', 'true', sql 'lower(username) LIKE', \"%$qs%"),
+             ), ') x(prio, btype, iid, title)
+           ORDER BY prio, btype, title'
     )
 };