Skip to content

Instantly share code, notes, and snippets.

@blowmage
Forked from agraves/user_search.rb
Last active December 12, 2015 09:59
Show Gist options
  • Save blowmage/4755700 to your computer and use it in GitHub Desktop.
Save blowmage/4755700 to your computer and use it in GitHub Desktop.
[vagrant@precise32:/vagrant (master)]$ echo "using search_exec"
using search_exec
[vagrant@precise32:/vagrant (master)]$ rspec spec/models/user_search_spec.rb
/usr/local/rvm/gems/ruby-1.9.3-p374/gems/activesupport-3.2.11/lib/active_support/dependencies.rb:251:in `block in require': iconv will be deprecated in the future, use String#encode instead.
/vagrant/vendor/gems/message_bus/lib/message_bus.rb:130: warning: already initialized constant ENCODE_SITE_TOKEN
== Seed from /vagrant/db/fixtures/post_action_types.rb
- PostActionType {:id=>1, :name_key=>"bookmark", :is_flag=>false, :position=>1}
- PostActionType {:id=>2, :name_key=>"like", :is_flag=>false, :icon=>"heart", :position=>2}
- PostActionType {:id=>3, :name_key=>"off_topic", :is_flag=>true, :position=>3}
- PostActionType {:id=>4, :name_key=>"inappropriate", :is_flag=>true, :position=>4}
- PostActionType {:id=>5, :name_key=>"vote", :is_flag=>false, :position=>5}
- PostActionType {:id=>8, :name_key=>"spam", :is_flag=>true, :position=>6}
- PostActionType {:id=>6, :name_key=>"custom_flag", :is_flag=>true, :position=>7}
.........
Finished in 7.33 seconds
9 examples, 0 failures
[vagrant@precise32:/vagrant (master)]$ echo "using search_ar"
using search_ar
[vagrant@precise32:/vagrant (master)]$ rspec spec/models/user_search_spec.rb
/usr/local/rvm/gems/ruby-1.9.3-p374/gems/activesupport-3.2.11/lib/active_support/dependencies.rb:251:in `block in require': iconv will be deprecated in the future, use String#encode instead.
/vagrant/vendor/gems/message_bus/lib/message_bus.rb:130: warning: already initialized constant ENCODE_SITE_TOKEN
== Seed from /vagrant/db/fixtures/post_action_types.rb
- PostActionType {:id=>1, :name_key=>"bookmark", :is_flag=>false, :position=>1}
- PostActionType {:id=>2, :name_key=>"like", :is_flag=>false, :icon=>"heart", :position=>2}
- PostActionType {:id=>3, :name_key=>"off_topic", :is_flag=>true, :position=>3}
- PostActionType {:id=>4, :name_key=>"inappropriate", :is_flag=>true, :position=>4}
- PostActionType {:id=>5, :name_key=>"vote", :is_flag=>false, :position=>5}
- PostActionType {:id=>8, :name_key=>"spam", :is_flag=>true, :position=>6}
- PostActionType {:id=>6, :name_key=>"custom_flag", :is_flag=>true, :position=>7}
.........
Finished in 6.87 seconds
9 examples, 0 failures
[vagrant@precise32:/vagrant (master)]$
class UserSearch
# This is a helper method to make your code prettier.
# Uses the default search method.
def self.search term, topic_id = nil
# UserSearch.new(term, topic_id).search_exec
UserSearch.new(term, topic_id).search_ar
end
def initialize term, topic_id
@term, @topic_id = term, topic_id
end
def search_exec
sql = User.sql_builder(
"select id, username, name, email from users u
/*left_join*/
/*where*/
/*order_by*/")
if @topic_id.present?
sql.left_join "(select distinct p.user_id from posts p where @topic_id = :topic_id) s on s.user_id = u.id", topic_id: @topic_id
end
if @term.present?
sql.where("username_lower like :term_like or
to_tsvector('simple', name) @@
to_tsquery('simple',
regexp_replace(
regexp_replace(
cast(plainto_tsquery(:term) as text)
,'\''(?: |$)', ':*''', 'g'),
'''', '', 'g')
)", term: @term, term_like: "#{@term.downcase}%")
sql.order_by "case when username_lower = :term then 0 else 1 end asc"
end
if @topic_id.present?
sql.order_by "case when s.user_id is null then 0 else 1 end desc"
end
sql.order_by "case when last_seen_at is null then 0 else 1 end desc, last_seen_at desc, username asc limit(20)"
sql.exec
end
def search_ar
scope = User.select [:id, :name, :username, :email]
if @term.present?
scope = scope.select select_username_exact_match_sql
scope = scope.where where_username_search_sql, term: @term, term_like: "#{@term.downcase}%"
scope = scope.order "username_exact_match DESC"
end
if @topic_id.present?
scope = scope.select select_user_in_topic_sql
scope = scope.order "user_in_topic DESC"
end
scope = scope.order "CASE WHEN last_seen_at IS NULL THEN 0 ELSE 1 END DESC"
scope = scope.order "last_seen_at DESC"
scope = scope.order "username ASC"
scope = scope.limit(20)
scope
end
private
def select_username_exact_match_sql
sql = "CASE WHEN username_lower = :term_lower THEN 1 ELSE 0 END AS username_exact_match"
# FWIW, rather have an index on LOWER(username) than the username_lower column
# sql = "CASE WHEN LOWER(username) = LOWER(:term) THEN 1 ELSE 0 END AS username_exact_match"
sanitize_sql_array sql, term_lower: @term.downcase
end
def select_user_in_topic_sql
sql = "CASE WHEN (SELECT COUNT(posts.user_id) FROM posts WHERE posts.user_id = users.id AND posts.topic_id = :topic_id) > 0 THEN 1 ELSE 0 END AS user_in_topic"
sanitize_sql_array sql, topic_id: @topic_id
end
def where_username_search_sql
# Do you need both the LIKE *or* the TSEARCH? Can't you just have the TSEARCH?
# Or, are you trying to avoid the TSEARCH by matching the LIKE first?
# I would think a properly indexed TSEARCH would be faster than a LIKE.
# The intent is hard to derive here...
# I'd still prefer an index on LOWER(username) to a username_lower column
<<-SQL
username_lower LIKE :term_like OR
TO_TSVECTOR('simple', name) @@
TO_TSQUERY('simple',
REGEXP_REPLACE(
REGEXP_REPLACE(
CAST(PLAINTO_TSQUERY(:term) AS text)
,'\''(?: |$)', ':*''', 'g'),
'''', '', 'g')
)
SQL
end
def sanitize_sql_array *args
ActiveRecord::Base.send(:sanitize_sql_array, args)
end
end
@blowmage
Copy link
Author

Attention @agraves @SamSaffron

@agraves
Copy link

agraves commented Feb 11, 2013

As a purely cosmetic concern, the repetition of scope in 50-67 feels a bit un-DRY.

I also worry that as this pattern proliferates throughout the codebase, requiring an explicit invocation of the sanitization (76, 81) routines invites errors of omission...but I suppose that comes with the territory, as this problem clearly required hand-rolled SQL.

Other than that I fully support this code. It addresses all of my root concerns about the original as-is.

@jcolebrand
Copy link

From line 84:

  def where_username_search_sql
    # Do you need both the LIKE *or* the TSEARCH? Can't you just have the TSEARCH?
    # Or, are you trying to avoid the TSEARCH by matching the LIKE first?
    # I would think a properly indexed TSEARCH would be faster than a LIKE.
    # The intent is hard to derive here...
    # I'd still prefer an index on LOWER(username) to a username_lower column

If this is a legitimate indexing concern, and the raw SQL were captured, I could find some dbas to give their input ;-)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment