gmnifaq

Unnamed repository; edit this file 'description' to name the repository.
git clone git://code.clttr.info/gmnifaq.git
Log | Files | Refs | README | LICENSE

commit 98ab6ca1ab3770d925bed026084215e36cf65913
parent 46ca4c42e2fa4202c1574d014a18460316505f42
Author: René Wagner <rwagner@rw-net.de>
Date:   Fri, 26 Feb 2021 18:02:22 +0100

FTS using AND matching

Diffstat:
Mfaqs.pl | 4++--
Msearch.pl | 7++++---
2 files changed, 6 insertions(+), 5 deletions(-)

diff --git a/faqs.pl b/faqs.pl @@ -63,12 +63,12 @@ sub faqs my @tags = $dbh->selectall_array("SELECT id, name FROM tags t LEFT JOIN tags_questions tq ON tq.t_id = t.id WHERE tq.q_id = @$_[0];"); if ( scalar @tags ) { - push @return, ('', 'Tags:'); + push @return, ('', '### Tags'); foreach (@tags) { push @return, sprintf("=> ./faqs.pl?tag=%d %s", @$_[0], @$_[1]); } } - push @return, ('', @$_[2], ''); + push @return, ('', '### Answer', @$_[2], ''); } } $dbh->disconnect(); diff --git a/search.pl b/search.pl @@ -24,7 +24,7 @@ if (!defined($ENV{'SERVER_PROTOCOL'}) || $ENV{'SERVER_PROTOCOL'} ne 'GEMINI') { my $query = lc(uri_unescape($ENV{'QUERY_STRING'})); if ($query eq '' || $query !~ /^[0-9a-z\s]*$/i) { - write_response('INPUT', 'Search faqs for the following terms (separate by blank)', undef); + write_response('INPUT', 'Search faqs for the following terms (separate by blank, all terms must match)', undef); } my @body = (); @@ -42,8 +42,9 @@ sub search my @result = (); my $dbh = DBI->connect($CONF{'dsn'}, '', '', { RaiseError => 1 }) or die $DBI::errstr; - my @matchingtags = $dbh->selectall_array("SELECT DISTINCT t.*, count(tq.q_id) AS count FROM fts_data INNER JOIN tags t ON t.id = fts_data.t_id LEFT JOIN tags_questions tq ON tq.t_id = t.id WHERE fts_data MATCH '\"text\": \"$term\"' GROUP BY t.id ORDER BY rank"); - my @matchingfaqs = $dbh->selectall_array("SELECT DISTINCT q_id, q.question FROM fts_data INNER JOIN questions q ON q.id = q_id WHERE fts_data MATCH '\"text\": \"$term\"' ORDER BY rank;"); + my $fts_search = join(' AND ', split(/ /, $term)); + my @matchingtags = $dbh->selectall_array("SELECT DISTINCT t.*, count(tq.q_id) AS count FROM fts_data INNER JOIN tags t ON t.id = fts_data.t_id LEFT JOIN tags_questions tq ON tq.t_id = t.id WHERE fts_data MATCH '$fts_search' GROUP BY t.id ORDER BY rank"); + my @matchingfaqs = $dbh->selectall_array("SELECT DISTINCT q_id, q.question FROM fts_data INNER JOIN questions q ON q.id = q_id WHERE fts_data MATCH '$fts_search' ORDER BY rank;"); $dbh->disconnect(); if ( !scalar @matchingtags && !scalar @matchingfaqs) {