terminal.output

[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)]$

user_search.rb

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

Attention @agraves @SamSaffron

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.

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 ;-)