about summary refs log tree commit diff
path: root/app/models
diff options
context:
space:
mode:
authorEugen Rochko <eugen@zeonfederated.com>2017-03-17 20:47:38 +0100
committerEugen Rochko <eugen@zeonfederated.com>2017-03-17 20:48:14 +0100
commitad0d82d3cee27839371b3182b434d8e78092890a (patch)
tree1028396d66d956c2a1ed696e59a9e4c41c123fed /app/models
parent22f9399cc30b2fa41a2813ccf559b7fd05be251d (diff)
Make account search blazing fast and rank followers/followees higher in the results
Diffstat (limited to 'app/models')
-rw-r--r--app/models/account.rb40
1 files changed, 36 insertions, 4 deletions
diff --git a/app/models/account.rb b/app/models/account.rb
index 1eb886ee3..978dc2d71 100644
--- a/app/models/account.rb
+++ b/app/models/account.rb
@@ -2,7 +2,6 @@
 
 class Account < ApplicationRecord
   include Targetable
-  include PgSearch
 
   MENTION_RE = /(?:^|[^\/\w])@([a-z0-9_]+(?:@[a-z0-9\.\-]+[a-z0-9]+)?)/i
   IMAGE_MIME_TYPES = ['image/jpeg', 'image/png', 'image/gif'].freeze
@@ -56,9 +55,6 @@ class Account < ApplicationRecord
   # PuSH subscriptions
   has_many :subscriptions, dependent: :destroy
 
-  pg_search_scope :search_for, against: { display_name: 'A', username: 'B', domain: 'C' },
-                               using: { tsearch: { prefix: true } }
-
   scope :remote, -> { where.not(domain: nil) }
   scope :local, -> { where(domain: nil) }
   scope :without_followers, -> { where('(select count(f.id) from follows as f where f.target_account_id = accounts.id) = 0') }
@@ -212,6 +208,42 @@ SQL
       Account.find_by_sql([sql, account.id, account.id, limit])
     end
 
+    def search_for(terms, limit = 10)
+      textsearch  = '(setweight(to_tsvector(\'simple\', accounts.display_name), \'A\') || setweight(to_tsvector(\'simple\', accounts.username), \'B\') || setweight(to_tsvector(\'simple\', coalesce(accounts.domain, \'\')), \'C\'))'
+      query       = 'to_tsquery(\'simple\', \'\'\' \' || ? || \' \'\'\' || \':*\')'
+
+      sql = <<SQL
+        SELECT
+          accounts.*,
+          ts_rank_cd(#{textsearch}, #{query}, 32) AS rank
+        FROM accounts
+        WHERE #{query} @@ #{textsearch}
+        ORDER BY rank DESC
+        LIMIT ?
+SQL
+
+      Account.find_by_sql([sql, terms, terms, limit])
+    end
+
+    def advanced_search_for(terms, account, limit = 10)
+      textsearch  = '(setweight(to_tsvector(\'simple\', accounts.display_name), \'A\') || setweight(to_tsvector(\'simple\', accounts.username), \'B\') || setweight(to_tsvector(\'simple\', coalesce(accounts.domain, \'\')), \'C\'))'
+      query       = 'to_tsquery(\'simple\', \'\'\' \' || ? || \' \'\'\' || \':*\')'
+
+      sql = <<SQL
+        SELECT
+          accounts.*,
+          (count(f.id) + 1) * ts_rank_cd(#{textsearch}, #{query}, 32) AS rank
+        FROM accounts
+        LEFT OUTER JOIN follows AS f ON (accounts.id = f.account_id AND f.target_account_id = ?) OR (accounts.id = f.target_account_id AND f.account_id = ?)
+        WHERE #{query} @@ #{textsearch}
+        GROUP BY accounts.id
+        ORDER BY rank DESC
+        LIMIT ?
+SQL
+
+      Account.find_by_sql([sql, terms, account.id, account.id, terms, limit])
+    end
+
     def following_map(target_account_ids, account_id)
       follow_mapping(Follow.where(target_account_id: target_account_ids, account_id: account_id), :target_account_id)
     end