From fc1c79bb0f3fd8b35ef6e1576b4b89aa666bd56b Mon Sep 17 00:00:00 2001 From: Starfall Date: Thu, 10 Nov 2022 11:39:13 -0600 Subject: Remove boost parameters from SQL-only account search --- app/models/account.rb | 18 +++++------------- 1 file changed, 5 insertions(+), 13 deletions(-) (limited to 'app/models/account.rb') diff --git a/app/models/account.rb b/app/models/account.rb index 7059c555f..6b3bd3af5 100644 --- a/app/models/account.rb +++ b/app/models/account.rb @@ -404,11 +404,6 @@ 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'))" - REPUTATION_SCORE_FUNCTION = '(greatest(0, coalesce(s.followers_count, 0)) / (greatest(0, coalesce(s.following_count, 0)) + 1.0))' - FOLLOWERS_SCORE_FUNCTION = 'log(greatest(0, coalesce(s.followers_count, 0)) + 2)' - 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 = "((#{REPUTATION_SCORE_FUNCTION} + #{FOLLOWERS_SCORE_FUNCTION} + #{TIME_DISTANCE_FUNCTION}) / 3.0)" - def readonly_attributes super - %w(statuses_count following_count followers_count) end @@ -424,10 +419,9 @@ class Account < ApplicationRecord sql = <<-SQL.squish SELECT accounts.*, - #{BOOST} * ts_rank_cd(#{TEXTSEARCH}, to_tsquery('simple', :tsquery), 32) AS rank + 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,15 +483,14 @@ class Account < ApplicationRecord ) SELECT accounts.*, - (count(f.id) + 1) * #{BOOST} * ts_rank_cd(#{TEXTSEARCH}, to_tsquery('simple', :tsquery), 32) AS rank + (count(f.id) + 1) * 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, s.id + GROUP BY accounts.id ORDER BY rank DESC LIMIT :limit OFFSET :offset SQL @@ -505,16 +498,15 @@ class Account < ApplicationRecord <<-SQL.squish SELECT accounts.*, - (count(f.id) + 1) * #{BOOST} * ts_rank_cd(#{TEXTSEARCH}, to_tsquery('simple', :tsquery), 32) AS rank + (count(f.id) + 1) * 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) 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 AND (accounts.domain IS NOT NULL OR (users.approved = TRUE AND users.confirmed_at IS NOT NULL)) - GROUP BY accounts.id, s.id + GROUP BY accounts.id ORDER BY rank DESC LIMIT :limit OFFSET :offset SQL -- cgit