restore recent-post boost for sql account search, fix search

1 files 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