From 267b7065444b2904e1172817c661709e0b456b80 Mon Sep 17 00:00:00 2001 From: Jebbs Date: Tue, 12 Feb 2019 14:20:07 +0800 Subject: Sort search results by relevance --- database/migration.go | 2 +- database/sql.go | 2 ++ database/sql/schema_version_22.sql | 1 + storage/entry.go | 6 +++--- storage/entry_query_builder.go | 5 ++++- ui/search_entries.go | 2 -- 6 files changed, 11 insertions(+), 7 deletions(-) create mode 100644 database/sql/schema_version_22.sql diff --git a/database/migration.go b/database/migration.go index 39d15b7..7e37dc6 100644 --- a/database/migration.go +++ b/database/migration.go @@ -12,7 +12,7 @@ import ( "miniflux.app/logger" ) -const schemaVersion = 21 +const schemaVersion = 22 // Migrate executes database migrations. func Migrate(db *sql.DB) { diff --git a/database/sql.go b/database/sql.go index 817feba..d4002c8 100644 --- a/database/sql.go +++ b/database/sql.go @@ -145,6 +145,7 @@ create index users_extra_idx on users using gin(extra); update entries set document_vectors = to_tsvector(substring(title || ' ' || coalesce(content, '') for 1000000)); create index document_vectors_idx on entries using gin(document_vectors);`, "schema_version_21": `alter table feeds add column user_agent text default '';`, + "schema_version_22": `update entries set document_vectors = setweight(to_tsvector(substring(coalesce(title, '') for 1000000)), 'A') || setweight(to_tsvector(substring(coalesce(content, '') for 1000000)), 'B');`, "schema_version_3": `create table tokens ( id text not null, value text not null, @@ -194,6 +195,7 @@ var SqlMapChecksums = map[string]string{ "schema_version_2": "e8e9ff32478df04fcddad10a34cba2e8bb1e67e7977b5bd6cdc4c31ec94282b4", "schema_version_20": "5d414c0cfc0da2863c641079afa58b7ff42dccb0f0e01c822ad435c3e3aa9201", "schema_version_21": "77da01ee38918ff4fe33985fbb20ed3276a717a7584c2ca9ebcf4d4ab6cb6910", + "schema_version_22": "51ed5fbcae9877e57274511f0ef8c61d254ebd78dfbcbc043a2acd30f4c93ca3", "schema_version_3": "a54745dbc1c51c000f74d4e5068f1e2f43e83309f023415b1749a47d5c1e0f12", "schema_version_4": "216ea3a7d3e1704e40c797b5dc47456517c27dbb6ca98bf88812f4f63d74b5d9", "schema_version_5": "46397e2f5f2c82116786127e9f6a403e975b14d2ca7b652a48cd1ba843e6a27c", diff --git a/database/sql/schema_version_22.sql b/database/sql/schema_version_22.sql new file mode 100644 index 0000000..1b51e8a --- /dev/null +++ b/database/sql/schema_version_22.sql @@ -0,0 +1 @@ +update entries set document_vectors = setweight(to_tsvector(substring(coalesce(title, '') for 1000000)), 'A') || setweight(to_tsvector(substring(coalesce(content, '') for 1000000)), 'B'); \ No newline at end of file diff --git a/storage/entry.go b/storage/entry.go index 7b80ca0..ec31843 100644 --- a/storage/entry.go +++ b/storage/entry.go @@ -50,7 +50,7 @@ func (s *Storage) UpdateEntryContent(entry *model.Entry) error { query := ` UPDATE entries - SET document_vectors = to_tsvector(substring(title || ' ' || coalesce(content, '') for 1000000)) + SET document_vectors = setweight(to_tsvector(substring(coalesce(title, '') for 1000000)), 'A') || setweight(to_tsvector(substring(coalesce(content, '') for 1000000)), 'B') WHERE id=$1 AND user_id=$2 ` _, err = tx.Exec(query, entry.ID, entry.UserID) @@ -68,7 +68,7 @@ func (s *Storage) createEntry(entry *model.Entry) error { INSERT INTO entries (title, hash, url, comments_url, published_at, content, author, user_id, feed_id, document_vectors) VALUES - ($1, $2, $3, $4, $5, $6, $7, $8, $9, to_tsvector(substring($1 || ' ' || coalesce($6, '') for 1000000))) + ($1, $2, $3, $4, $5, $6, $7, $8, $9, setweight(to_tsvector(substring(coalesce($1, '') for 1000000)), 'A') || setweight(to_tsvector(substring(coalesce($6, '') for 1000000)), 'B')) RETURNING id, status ` err := s.db.QueryRow( @@ -107,7 +107,7 @@ func (s *Storage) updateEntry(entry *model.Entry) error { query := ` UPDATE entries SET title=$1, url=$2, comments_url=$3, content=$4, author=$5, - document_vectors=to_tsvector(substring($1 || ' ' || coalesce($4, '') for 1000000)) + document_vectors = setweight(to_tsvector(substring(coalesce($1, '') for 1000000)), 'A') || setweight(to_tsvector(substring(coalesce($4, '') for 1000000)), 'B') WHERE user_id=$6 AND feed_id=$7 AND hash=$8 RETURNING id ` diff --git a/storage/entry_query_builder.go b/storage/entry_query_builder.go index 192f515..c4f6e43 100644 --- a/storage/entry_query_builder.go +++ b/storage/entry_query_builder.go @@ -33,6 +33,9 @@ func (e *EntryQueryBuilder) WithSearchQuery(query string) *EntryQueryBuilder { e.conditions = append(e.conditions, fmt.Sprintf("e.document_vectors @@ plainto_tsquery($%d)", len(e.args)+1)) e.args = append(e.args, query) } + // ordered by relevance, can be overrode + e.WithOrder(fmt.Sprintf("ts_rank(document_vectors, plainto_tsquery('%s'))", query)) + e.WithDirection("DESC") return e } @@ -315,7 +318,7 @@ func (e *EntryQueryBuilder) buildSorting() string { var parts []string if e.order != "" { - parts = append(parts, fmt.Sprintf(`ORDER BY "%s"`, e.order)) + parts = append(parts, fmt.Sprintf(`ORDER BY %s`, e.order)) } if e.direction != "" { diff --git a/ui/search_entries.go b/ui/search_entries.go index be97da0..88a60f5 100644 --- a/ui/search_entries.go +++ b/ui/search_entries.go @@ -27,8 +27,6 @@ func (h *handler) showSearchEntriesPage(w http.ResponseWriter, r *http.Request) builder := h.store.NewEntryQueryBuilder(user.ID) builder.WithSearchQuery(searchQuery) builder.WithoutStatus(model.EntryStatusRemoved) - builder.WithOrder(model.DefaultSortingOrder) - builder.WithDirection(user.EntryDirection) builder.WithOffset(offset) builder.WithLimit(nbItemsPerPage) -- cgit v1.2.3