Add database index for full text search.

1 files changed, 22 insertions, 0 deletions

diff --git a/perllib/FixMyStreet/Roles/FullTextSearch.pm b/perllib/FixMyStreet/Roles/FullTextSearch.pm
new file mode 100644
index 000000000..78dbd5c18
--- /dev/null
+++ b/perllib/FixMyStreet/Roles/FullTextSearch.pm
@@ -0,0 +1,22 @@
+package FixMyStreet::Roles::FullTextSearch;
+
+use Moo::Role;
+use FixMyStreet;
+
+requires 'text_search_columns';
+requires 'text_search_nulls';
+
+sub search_text {
+    my ($rs, $query) = @_;
+    my %nulls = map { $_ => 1 } $rs->text_search_nulls;
+    my @cols = map {
+        my $col = $rs->me($_);
+        $nulls{$_} ? "coalesce($col, '')" : $col;
+    } $rs->text_search_columns;
+    my $vector = "translate(" . join(" || ' ' || ", @cols) . ", '/.', '  ')";
+    my $config = FixMyStreet->config('DB_FULL_TEXT_SEARCH_CONFIG') || 'english';
+    $rs->search(\[ "to_tsvector('$config', $vector) @@ plainto_tsquery('$config', ?)", $query ]);
+}
+
+1;
+