summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2016-07-03 15:14:49 +0200
committerYorhel <git@yorhel.nl>2016-07-03 15:14:49 +0200
commite2cd8349be295dff3fd2b07f1b1c8282377a1ffd (patch)
tree6201ae5b4635359222999b4f82b4227f8f26116e /util
parent5648ca9997e4d986799bccd877deaf72b47e48a0 (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 'util')
-rw-r--r--util/sql/func.sql15
-rw-r--r--util/updates/update_2.26.sql11
2 files changed, 26 insertions, 0 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 125455a2..ec109692 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -31,6 +31,21 @@ CREATE OR REPLACE FUNCTION strip_spoilers(t text) RETURNS text AS $$
$$ LANGUAGE sql IMMUTABLE;
+-- Assigns a score to the relevance of a substring match, intended for use in
+-- an ORDER BY clause. Exact matches are ordered first, prefix matches after
+-- that, and finally a normal substring match. Not particularly fast, but
+-- that's to be expected of naive substring searches.
+-- Pattern must be escaped for use as a LIKE pattern.
+CREATE OR REPLACE FUNCTION substr_score(str text, pattern text) RETURNS integer AS $$
+SELECT CASE
+ WHEN str ILIKE pattern THEN 0
+ WHEN str ILIKE pattern||'%' THEN 1
+ WHEN str ILIKE '%'||pattern||'%' THEN 2
+ ELSE 3
+END;
+$$ LANGUAGE SQL;
+
+
-- update_vncache(id) - updates some c_* columns in the vn table
CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
UPDATE vn SET
diff --git a/util/updates/update_2.26.sql b/util/updates/update_2.26.sql
index 444666f8..2d5251a6 100644
--- a/util/updates/update_2.26.sql
+++ b/util/updates/update_2.26.sql
@@ -33,3 +33,14 @@ ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_optid_fke
-- Tagalog language
ALTER TYPE language ADD VALUE 'ta' BEFORE 'tr';
+
+
+-- Improved substring search relevance
+CREATE OR REPLACE FUNCTION substr_score(str text, pattern text) RETURNS integer AS $$
+SELECT CASE
+ WHEN str ILIKE pattern THEN 0
+ WHEN str ILIKE pattern||'%' THEN 1
+ WHEN str ILIKE '%'||pattern||'%' THEN 2
+ ELSE 3
+END;
+$$ LANGUAGE SQL;