summaryrefslogtreecommitdiff
path: root/lib/VNWeb
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2021-11-19 13:33:19 +0100
committerYorhel <git@yorhel.nl>2021-11-19 13:33:19 +0100
commit0a98d9df12cfc6ea73729b0b3feb3fee52c9f336 (patch)
tree777d2b1fd12a7f98825c0c2fef6ff87650867e14 /lib/VNWeb
parent6560748025427a7bca6d074a4858a79a7e7ec48c (diff)
Search adjustments + use new search for producers,staff,characters too
Diffstat (limited to 'lib/VNWeb')
-rw-r--r--lib/VNWeb/Chars/Elm.pm5
-rw-r--r--lib/VNWeb/Chars/List.pm8
-rw-r--r--lib/VNWeb/Producers/Elm.pm13
-rw-r--r--lib/VNWeb/Producers/List.pm3
-rw-r--r--lib/VNWeb/Staff/Elm.pm5
-rw-r--r--lib/VNWeb/Staff/List.pm8
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, ')') : ();