diff options
author | Yorhel <git@yorhel.nl> | 2021-11-19 13:33:19 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2021-11-19 13:33:19 +0100 |
commit | 0a98d9df12cfc6ea73729b0b3feb3fee52c9f336 (patch) | |
tree | 777d2b1fd12a7f98825c0c2fef6ff87650867e14 /lib/VNWeb | |
parent | 6560748025427a7bca6d074a4858a79a7e7ec48c (diff) |
Search adjustments + use new search for producers,staff,characters too
Diffstat (limited to 'lib/VNWeb')
-rw-r--r-- | lib/VNWeb/Chars/Elm.pm | 5 | ||||
-rw-r--r-- | lib/VNWeb/Chars/List.pm | 8 | ||||
-rw-r--r-- | lib/VNWeb/Producers/Elm.pm | 13 | ||||
-rw-r--r-- | lib/VNWeb/Producers/List.pm | 3 | ||||
-rw-r--r-- | lib/VNWeb/Staff/Elm.pm | 5 | ||||
-rw-r--r-- | lib/VNWeb/Staff/List.pm | 8 |
6 files changed, 12 insertions, 30 deletions
diff --git a/lib/VNWeb/Chars/Elm.pm b/lib/VNWeb/Chars/Elm.pm index ce14f490..f52ee8f5 100644 --- a/lib/VNWeb/Chars/Elm.pm +++ b/lib/VNWeb/Chars/Elm.pm @@ -4,16 +4,13 @@ use VNWeb::Prelude; elm_api Chars => undef, { search => {} }, sub { my $q = shift->{search}; - my $qs = sql_like $q; my $l = tuwf->dbPagei({ results => 15, page => 1 }, 'SELECT c.id, c.name, c.original, c.main, cm.name AS main_name, cm.original AS main_original FROM (SELECT MIN(prio), id FROM (', sql_join('UNION ALL', $q =~ /^$RE{cid}$/ ? sql('SELECT 1, id FROM chars WHERE id =', \"$+{id}") : (), - sql('SELECT 1+substr_score(lower(name),' , \$qs, '), id FROM chars WHERE name ILIKE', \"%$qs%"), - sql('SELECT 10+substr_score(lower(original),', \$qs, "), id FROM chars WHERE translate(original,' ','') ILIKE", \("%$qs%" =~ s/ //gr)), - sql('SELECT 100, id FROM chars WHERE alias ILIKE', \"%$qs%"), + sql('SELECT 1+substr_score(lower(name),', \sql_like($q),'), id FROM chars WHERE c_search LIKE ALL (search_query(', \$q, '))'), ), ') x(prio,id) GROUP BY id) x(prio, id) JOIN chars c ON c.id = x.id LEFT JOIN chars cm ON cm.id = c.main diff --git a/lib/VNWeb/Chars/List.pm b/lib/VNWeb/Chars/List.pm index e4956ff7..e44e77ea 100644 --- a/lib/VNWeb/Chars/List.pm +++ b/lib/VNWeb/Chars/List.pm @@ -106,13 +106,9 @@ TUWF::get qr{/c(?:/(?<char>all|[a-z0]))?}, sub { $opt->{f} = advsearch_default 'c' if !$opt->{f}{query} && !defined tuwf->reqGet('f'); - my @search = map { - my $l = '%'.sql_like($_).'%'; - length $_ > 0 ? sql '(c.name ILIKE', \$l, "OR translate(c.original,' ','') ILIKE", \$l, "OR translate(c.alias,' ','') ILIKE", \$l, ')' : (); - } split /[ -,._]/, $opt->{q}||''; - my $where = sql_and - 'NOT c.hidden', $opt->{f}->sql_where(), @search, + 'NOT c.hidden', $opt->{f}->sql_where(), + $opt->{q} ? sql 'c.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (), defined($opt->{ch}) && $opt->{ch} ? sql('LOWER(SUBSTR(c.name, 1, 1)) =', \$opt->{ch}) : (), defined($opt->{ch}) && !$opt->{ch} ? sql('(ASCII(c.name) <', \97, 'OR ASCII(c.name) >', \122, ') AND (ASCII(c.name) <', \65, 'OR ASCII(c.name) >', \90, ')') : (); diff --git a/lib/VNWeb/Producers/Elm.pm b/lib/VNWeb/Producers/Elm.pm index dae9709d..2ffea46a 100644 --- a/lib/VNWeb/Producers/Elm.pm +++ b/lib/VNWeb/Producers/Elm.pm @@ -13,15 +13,10 @@ elm_api Producers => undef, { elm_ProducerResult tuwf->dbPagei({ results => 15, page => 1 }, 'SELECT p.id, p.name, p.original, p.hidden FROM (', - sql_join('UNION ALL', map { - my $qs = sql_like $_; - ( - /^$RE{pid}$/ ? sql('SELECT 1, id FROM producers WHERE id =', \"$+{id}") : (), - sql('SELECT 1+substr_score(lower(name),' , \$qs, '), id FROM producers WHERE name ILIKE', \"%$qs%"), - sql('SELECT 10+substr_score(lower(original),', \$qs, "), id FROM producers WHERE translate(original,' ','') ILIKE", \("%$qs%" =~ s/ //gr)), - sql('SELECT 100, id FROM producers WHERE alias ILIKE', \"%$qs%"), - ) - } @q), + sql_join('UNION ALL', map +( + /^$RE{pid}$/ ? sql('SELECT 1, id FROM producers WHERE id =', \"$+{id}") : (), + sql('SELECT 1+substr_score(lower(name),', \sql_like($_), '), id FROM producers WHERE c_search LIKE ALL (search_query(', \"$_", '))'), + ), @q), ') x(prio, id) JOIN producers p ON p.id = x.id WHERE', sql_and($data->{hidden} ? () : 'NOT p.hidden'), ' diff --git a/lib/VNWeb/Producers/List.pm b/lib/VNWeb/Producers/List.pm index 82282a23..741e74f6 100644 --- a/lib/VNWeb/Producers/List.pm +++ b/lib/VNWeb/Producers/List.pm @@ -39,9 +39,8 @@ TUWF::get qr{/p(?:/(?<char>all|[a-z0]))?}, sub { $opt->{f} = advsearch_default 'p' if !$opt->{f}{query} && !defined tuwf->reqGet('f'); - my $qs = length $opt->{q} && '%'.sql_like($opt->{q}).'%'; my $where = sql_and 'NOT p.hidden', $opt->{f}->sql_where(), - $qs ? sql('p.name ILIKE', \$qs, 'OR p.original ILIKE', \$qs, 'OR p.alias ILIKE', \$qs) : (), + $opt->{q} ? sql 'p.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (), defined($opt->{ch}) && $opt->{ch} ? sql('LOWER(SUBSTR(p.name, 1, 1)) =', \$opt->{ch}) : (), defined($opt->{ch}) && !$opt->{ch} ? sql('(ASCII(p.name) <', \97, 'OR ASCII(p.name) >', \122, ') AND (ASCII(p.name) <', \65, 'OR ASCII(p.name) >', \90, ')') : (); diff --git a/lib/VNWeb/Staff/Elm.pm b/lib/VNWeb/Staff/Elm.pm index c4db154f..5318f458 100644 --- a/lib/VNWeb/Staff/Elm.pm +++ b/lib/VNWeb/Staff/Elm.pm @@ -4,15 +4,14 @@ use VNWeb::Prelude; elm_api Staff => undef, { search => {} }, sub { my $q = shift->{search}; - my $qs = sql_like $q; elm_StaffResult tuwf->dbPagei({ results => 15, page => 1 }, 'SELECT s.id, sa.aid, sa.name, sa.original FROM (', sql_join('UNION ALL', $q =~ /^$RE{sid}$/ ? sql('SELECT 0, aid FROM staff_alias WHERE id =', \"$+{id}") : (), - sql('SELECT 1+substr_score(lower(name),', \$qs, ')+substr_score(lower(original),', \$qs, '), aid - FROM staff_alias WHERE name ILIKE', \"%$qs%", "OR translate(original,' ','') ILIKE", \("%$qs%" =~ s/ //gr)), + sql('SELECT 1+substr_score(lower(name),', \sql_like($q), ')+substr_score(lower(original),', \sql_like($q), '), aid + FROM staff_alias WHERE c_search LIKE ALL (search_query(', \$q, '))'), ), ') x(prio, aid) JOIN staff_alias sa ON sa.aid = x.aid JOIN staff s ON s.id = sa.id diff --git a/lib/VNWeb/Staff/List.pm b/lib/VNWeb/Staff/List.pm index d83e7827..77f930cd 100644 --- a/lib/VNWeb/Staff/List.pm +++ b/lib/VNWeb/Staff/List.pm @@ -51,14 +51,10 @@ TUWF::get qr{/s(?:/(?<char>all|[a-z0]))?}, sub { $opt->{f} = advsearch_default 's' if !$opt->{f}{query} && !defined tuwf->reqGet('f'); - my @search = map { - my $l = '%'.sql_like($_).'%'; - length $_ > 0 ? sql '(sa.name ILIKE', \$l, "OR translate(sa.original,' ','') ILIKE", \$l, ')' : (); - } split /[ -,._]/, $opt->{q}||''; - my $where = sql_and $opt->{n} ? 's.aid = sa.aid' : (), - 'NOT s.hidden', $opt->{f}->sql_where(), @search, + 'NOT s.hidden', $opt->{f}->sql_where(), + $opt->{q} ? sql 'sa.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (), defined($opt->{ch}) && $opt->{ch} ? sql('LOWER(SUBSTR(sa.name, 1, 1)) =', \$opt->{ch}) : (), defined($opt->{ch}) && !$opt->{ch} ? sql('(ASCII(sa.name) <', \97, 'OR ASCII(sa.name) >', \122, ') AND (ASCII(sa.name) <', \65, 'OR ASCII(sa.name) >', \90, ')') : (); |