diff options
| -rw-r--r-- | Gemfile | 1 | ||||
| -rw-r--r-- | Gemfile.lock | 5 | ||||
| -rw-r--r-- | app/models/account.rb | 40 | ||||
| -rw-r--r-- | app/services/search_service.rb | 7 | ||||
| -rw-r--r-- | db/migrate/20170317193015_add_search_index_to_accounts.rb | 9 | ||||
| -rw-r--r-- | db/schema.rb | 3 |
6 files changed, 50 insertions, 15 deletions
diff --git a/Gemfile b/Gemfile index c97f80bde..56a8a6594 100644 --- a/Gemfile +++ b/Gemfile @@ -47,7 +47,6 @@ gem 'rack-attack' gem 'rack-cors', require: 'rack/cors' gem 'sidekiq' gem 'rails-settings-cached' -gem 'pg_search' gem 'simple-navigation' gem 'statsd-instrument' gem 'ruby-oembed', require: 'oembed' diff --git a/Gemfile.lock b/Gemfile.lock index 8ab50773f..c8a5991d0 100644 --- a/Gemfile.lock +++ b/Gemfile.lock @@ -254,10 +254,6 @@ GEM parser (2.3.1.2) ast (~> 2.2) pg (0.18.4) - pg_search (1.0.6) - activerecord (>= 3.1) - activesupport (>= 3.1) - arel pghero (1.6.2) activerecord powerpack (0.1.1) @@ -491,7 +487,6 @@ DEPENDENCIES paperclip (~> 5.1) paperclip-av-transcoder pg - pg_search pghero pry-rails puma 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 diff --git a/app/services/search_service.rb b/app/services/search_service.rb index 6f740e149..19fc16973 100644 --- a/app/services/search_service.rb +++ b/app/services/search_service.rb @@ -1,7 +1,7 @@ # frozen_string_literal: true class SearchService < BaseService - def call(query, limit, resolve = false) + def call(query, limit, resolve = false, account = nil) return if query.blank? || query.start_with?('#') username, domain = query.gsub(/\A@/, '').split('@') @@ -9,13 +9,12 @@ class SearchService < BaseService if domain.nil? exact_match = Account.find_local(username) - results = Account.search_for(username) + results = account.nil? ? Account.search_for(username, limit) : Account.advanced_search_for(username, account, limit) else exact_match = Account.find_remote(username, domain) - results = Account.search_for("#{username} #{domain}") + results = account.nil? ? Account.search_for("#{username} #{domain}", limit) : Account.advanced_search_for("#{username} #{domain}", account, limit) end - results = results.limit(limit).to_a results = [exact_match] + results.reject { |a| a.id == exact_match.id } if exact_match if resolve && !exact_match && !domain.nil? diff --git a/db/migrate/20170317193015_add_search_index_to_accounts.rb b/db/migrate/20170317193015_add_search_index_to_accounts.rb new file mode 100644 index 000000000..14e174147 --- /dev/null +++ b/db/migrate/20170317193015_add_search_index_to_accounts.rb @@ -0,0 +1,9 @@ +class AddSearchIndexToAccounts < ActiveRecord::Migration[5.0] + def up + execute 'CREATE INDEX search_index ON accounts USING gin((setweight(to_tsvector(\'simple\', accounts.display_name), \'A\') || setweight(to_tsvector(\'simple\', accounts.username), \'B\') || setweight(to_tsvector(\'simple\', coalesce(accounts.domain, \'\')), \'C\')));' + end + + def down + remove_index :accounts, name: :search_index + end +end diff --git a/db/schema.rb b/db/schema.rb index 4ec85ef2b..c571fa3a8 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -10,7 +10,7 @@ # # It's strongly recommended that you check this file into your version control system. -ActiveRecord::Schema.define(version: 20170304202101) do +ActiveRecord::Schema.define(version: 20170317193015) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -43,6 +43,7 @@ ActiveRecord::Schema.define(version: 20170304202101) do t.boolean "silenced", default: false, null: false t.boolean "suspended", default: false, null: false t.boolean "locked", default: false, null: false + t.index "(((setweight(to_tsvector('simple'::regconfig, (display_name)::text), 'A'::\"char\") || setweight(to_tsvector('simple'::regconfig, (username)::text), 'B'::\"char\")) || setweight(to_tsvector('simple'::regconfig, (COALESCE(domain, ''::character varying))::text), 'C'::\"char\")))", name: "search_index", using: :gin t.index ["username", "domain"], name: "index_accounts_on_username_and_domain", unique: true, using: :btree end |