From 65c1e53a32cabcdbb7bca57002bb0f6acdebe07e Mon Sep 17 00:00:00 2001 From: Starfall Date: Tue, 17 Jan 2023 18:24:57 -0600 Subject: restore recent-post boost for sql account search, fix search --- app/models/account.rb | 14 ++++++++++---- 1 file changed, 10 insertions(+), 4 deletions(-) diff --git a/app/models/account.rb b/app/models/account.rb index d9511999b..64a35aca0 100644 --- a/app/models/account.rb +++ b/app/models/account.rb @@ -410,6 +410,9 @@ class Account < ApplicationRecord DISALLOWED_TSQUERY_CHARACTERS = /['?\\:‘’]/.freeze TEXTSEARCH = "(setweight(to_tsvector('simple', accounts.display_name), 'A') || setweight(to_tsvector('simple', accounts.username), 'B') || setweight(to_tsvector('simple', coalesce(accounts.domain, '')), 'C'))" + TIME_DISTANCE_FUNCTION = '(case when s.last_status_at is null then 0 else exp(-1.0 * ((greatest(0, abs(extract(DAY FROM age(s.last_status_at))) - 30.0)^2) / (2.0 * ((-1.0 * 30^2) / (2.0 * ln(0.3)))))) end)' + BOOST = "(#{TIME_DISTANCE_FUNCTION})" + def readonly_attributes super - %w(statuses_count following_count followers_count) end @@ -425,9 +428,10 @@ class Account < ApplicationRecord sql = <<-SQL.squish SELECT accounts.*, - ts_rank_cd(#{TEXTSEARCH}, to_tsquery('simple', :tsquery), 32) AS rank + #{BOOST} * ts_rank_cd(#{TEXTSEARCH}, to_tsquery('simple', :tsquery), 32) AS rank FROM accounts LEFT JOIN users ON accounts.id = users.account_id + LEFT JOIN account_stats AS s ON accounts.id = s.account_id WHERE to_tsquery('simple', :tsquery) @@ #{TEXTSEARCH} AND accounts.suspended_at IS NULL AND accounts.moved_to_account_id IS NULL @@ -489,14 +493,15 @@ class Account < ApplicationRecord ) SELECT accounts.*, - (count(f.id) + 1) * ts_rank_cd(#{TEXTSEARCH}, to_tsquery('simple', :tsquery), 32) AS rank + (count(f.id) + 1) * #{BOOST} * ts_rank_cd(#{TEXTSEARCH}, to_tsquery('simple', :tsquery), 32) AS rank FROM accounts LEFT OUTER JOIN follows AS f ON (accounts.id = f.account_id AND f.target_account_id = :id) + LEFT JOIN account_stats AS s ON accounts.id = s.account_id WHERE accounts.id IN (SELECT * FROM first_degree) AND to_tsquery('simple', :tsquery) @@ #{TEXTSEARCH} AND accounts.suspended_at IS NULL AND accounts.moved_to_account_id IS NULL - GROUP BY accounts.id + GROUP BY accounts.id, s.id ORDER BY rank DESC LIMIT :limit OFFSET :offset SQL @@ -504,11 +509,12 @@ class Account < ApplicationRecord <<-SQL.squish SELECT accounts.*, - (count(f.id) + 1) * ts_rank_cd(#{TEXTSEARCH}, to_tsquery('simple', :tsquery), 32) AS rank, + #{BOOST} * ts_rank_cd(#{TEXTSEARCH}, to_tsquery('simple', :tsquery), 32) AS rank, count(f.id) AS followed FROM accounts LEFT OUTER JOIN follows AS f ON (accounts.id = f.account_id AND f.target_account_id = :id) OR (accounts.id = f.target_account_id AND f.account_id = :id) LEFT JOIN users ON accounts.id = users.account_id + LEFT JOIN account_stats AS s ON accounts.id = s.account_id WHERE to_tsquery('simple', :tsquery) @@ #{TEXTSEARCH} AND accounts.suspended_at IS NULL AND accounts.moved_to_account_id IS NULL -- cgit