diff options
author | Yorhel <git@yorhel.nl> | 2016-07-03 15:14:49 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2016-07-03 15:14:49 +0200 |
commit | e2cd8349be295dff3fd2b07f1b1c8282377a1ffd (patch) | |
tree | 6201ae5b4635359222999b4f82b4227f8f26116e /lib/VNDB/DB | |
parent | 5648ca9997e4d986799bccd877deaf72b47e48a0 (diff) |
Generalize substring search relevance + apply to most dropdown searches
This is a generalization of the search improvements made in
7da2edeaa0f6cf7794f4f8f68960497dc1be893c and
92235222dba4e5d0c7713d53ef12e0f10e371b83
And has been applied to the dropdown searches for producers, staff, tags
and traits.
For all those searches, exact matches are listed first, followed by
prefix matches, and then substring matches. Relevance is currently only
based on the primary name/title and ignores aliases (except for staff).
This is fixable, but not trivial, and I'm not sure it's all that useful.
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r-- | lib/VNDB/DB/Producers.pm | 14 | ||||
-rw-r--r-- | lib/VNDB/DB/Staff.pm | 15 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 11 | ||||
-rw-r--r-- | lib/VNDB/DB/Traits.pm | 13 |
4 files changed, 34 insertions, 19 deletions
diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm index 929e333b..133c3f31 100644 --- a/lib/VNDB/DB/Producers.pm +++ b/lib/VNDB/DB/Producers.pm @@ -8,7 +8,7 @@ use Exporter 'import'; our @EXPORT = qw|dbProducerGet dbProducerGetRev dbProducerRevisionInsert|; -# options: results, page, id, search, char +# options: results, page, id, search, char, sort # what: extended relations relgraph sub dbProducerGet { my $self = shift; @@ -40,13 +40,19 @@ sub dbProducerGet { $select .= ', p.desc, p.alias, p.website, p.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/; $select .= ', pg.svg' if $o{what} =~ /relgraph/; - my($r, $np) = $self->dbPage(\%o, q| + my($order, @order) = ('p.name'); + if($o{sort} && $o{sort} eq 'search') { + $order = 'least(substr_score(p.name, ?), substr_score(p.original, ?)), p.name'; + @order = ($o{search}) x 2; + } + + my($r, $np) = $self->dbPage(\%o, qq| SELECT !s FROM producers p !s !W - ORDER BY p.name ASC|, - $select, $join, \%where, + ORDER BY $order|, + $select, $join, \%where, @order ); return _enrich($self, $r, $np, 0, $o{what}); diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm index 45e6df46..bf2ae325 100644 --- a/lib/VNDB/DB/Staff.pm +++ b/lib/VNDB/DB/Staff.pm @@ -7,7 +7,7 @@ use Exporter 'import'; our @EXPORT = qw|dbStaffGet dbStaffGetRev dbStaffRevisionInsert dbStaffAliasIds|; -# options: results, page, id, aid, search, exact, truename, role, gender, notid +# options: results, page, id, aid, search, exact, truename, role, gender # what: extended changes roles aliases sub dbStaffGet { my $self = shift; @@ -35,7 +35,6 @@ sub dbStaffGet { $o{id} ? ( ref $o{id} ? ('s.id IN(!l)' => [$o{id}]) : ('s.id = ?' => $o{id}) ) : (), $o{aid} ? ( ref $o{aid} ? ('sa.aid IN(!l)' => [$o{aid}]) : ('sa.aid = ?' => $o{aid}) ) : (), $o{id} || $o{truename} ? ( 's.aid = sa.aid' => 1 ) : (), - $o{notid} && @{$o{notid}} ? ('s.id NOT IN(!l)' => [$o{notid}]) : (), defined $o{gender} ? ( 's.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (), defined $o{lang} ? ( 's.lang IN(!l)' => [ ref $o{lang} ? $o{lang} : [$o{lang}] ]) : (), defined $o{role} ? ( @@ -60,13 +59,19 @@ sub dbStaffGet { my $select = 's.id, sa.aid, sa.name, sa.original, s.gender, s.lang'; $select .= ', s.desc, s.l_wp, s.l_site, s.l_twitter, s.l_anidb, s.hidden, s.locked' if $o{what} =~ /extended/; - my($r, $np) = $self->dbPage(\%o, q| + my($order, @order) = ('sa.name'); + if($o{sort} && $o{sort} eq 'search') { + $order = 'least(substr_score(sa.name, ?), substr_score(sa.original, ?)), sa.name'; + @order = ($o{search}) x 2; + } + + my($r, $np) = $self->dbPage(\%o, qq| SELECT !s FROM staff s JOIN staff_alias sa ON sa.id = s.id !W - ORDER BY sa.name|, - $select, \%where + ORDER BY $order|, + $select, \%where, @order ); return _enrich($self, $r, $np, 0, $o{what}); diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index 10b45a4b..8c27e55f 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -10,7 +10,7 @@ our @EXPORT = qw|dbTagGet dbTTTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbTa # %options->{ id noid name search state meta page results what sort reverse } # what: parents childs(n) aliases addedby -# sort: id name added items +# sort: id name added items search sub dbTagGet { my $self = shift; my %o = ( @@ -50,15 +50,18 @@ sub dbTagGet { name => 't.name %s', added => 't.added %s', items => 't.c_items %s', + search=> 'substr_score(t.name, ?) ASC, t.name %s', # Assigning a matching score for aliases is also possible, but more involved }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; + my @order = $o{sort} && $o{sort} eq 'search' ? ($o{search}) : (); - my($r, $np) = $self->dbPage(\%o, q| + + my($r, $np) = $self->dbPage(\%o, qq| SELECT !s FROM tags t !s !W - ORDER BY !s|, - join(', ', @select), join(' ', @join), \%where, $order + ORDER BY $order|, + join(', ', @select), join(' ', @join), \%where, @order ); if(@$r && $o{what} =~ /aliases/) { diff --git a/lib/VNDB/DB/Traits.pm b/lib/VNDB/DB/Traits.pm index f2eadaba..728e4e85 100644 --- a/lib/VNDB/DB/Traits.pm +++ b/lib/VNDB/DB/Traits.pm @@ -15,7 +15,7 @@ our @EXPORT = qw|dbTraitGet dbTraitEdit dbTraitAdd|; # Options: id noid search name state what results page sort reverse # what: parents childs(n) addedby -# sort: id name name added items +# sort: id name name added items search sub dbTraitGet { my $self = shift; my %o = ( @@ -30,8 +30,7 @@ sub dbTraitGet { my %where = ( $o{id} ? ( 't.id IN(!l)' => [ ref($o{id}) ? $o{id} : [$o{id}] ]) : (), $o{group} ? ( 't.group = ?' => $o{group} ) : (), - $o{noid} && ref($o{noid}) && @{$o{noid}} ? ('t.id NOT IN(!l)', [$o{noid}]) : (), - $o{noid} && !ref($o{noid}) ? ('t.id <> ?' => $o{noid}) : (), + $o{noid} ? ( 't.id <> ?' => $o{noid} ) : (), defined $o{state} && $o{state} != -1 ? ( 't.state = ?' => $o{state} ) : (), !defined $o{state} && !$o{id} && !$o{name} ? ( @@ -56,15 +55,17 @@ sub dbTraitGet { group => 'tg."order" %s, t.name %1$s', added => 't.added %s', items => 't.c_items %s', + search=> 'substr_score(t.name, ?) ASC, t.name %s', # Can't score aliases at the moment }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; + my @order = $o{sort} && $o{sort} eq 'search' ? ($o{search}) : (); - my($r, $np) = $self->dbPage(\%o, q| + my($r, $np) = $self->dbPage(\%o, qq| SELECT !s FROM traits t !s !W - ORDER BY !s|, - join(', ', @select), join(' ', @join), \%where, $order + ORDER BY $order|, + join(', ', @select), join(' ', @join), \%where, @order, ); if($o{what} =~ /parents\((\d+)\)/) { |