Search multiple text_fields within the same model

Question

I'm developing a search form for my ruby application. I now want to search using 3 text_fields, 1 search button.

My code looks like this

Controller

def search
params_name = params[:name].upcase.gsub("'", "\\\'").gsub("\"", "\\\"")
params_username = params[:username].upcase.gsub("'", "\\\'").gsub("\"", "\\\"")
params_email = params[:email].upcase.gsub("'", "\\\'").gsub("\"", "\\\"")

where_clauses = []
where_clauses << "UPPER(name) LIKE '%#{params_name}%'" if params_name.present?
where_clauses << "UPPER(username) LIKE '%#{params_username}%'" if params_username.present?
where_clauses << "UPPER(email) LIKE '%#{params_email}%'" if params_email.present?

# where_clauses << "UPPER(email) LIKE '%#{params_name}%' OR UPPER(first_name) LIKE '%#{params_name}%' OR UPPER(last_name) LIKE '%#{params_name}%' OR UPPER(username) LIKE '%#{params_name}%'" if params_name.present?

User.set_per_page(params[:per_page]) if params[:per_page].present?

base_user = User.where(where_clauses.join(" AND "))
@users_count = base_user.count
@users = base_user.paginate(:page => params[:page])

render :index

Views

<div class="clearfix">
        <div class="form-group pull-right">
          <label class="right">Search</label>              
          <%= form_tag(search_users_path, :method => "get", id: "search-form") do %>
            <%= text_field_tag :name, params[:name], placeholder: "Search Users", class: "form-control" %>
            <%= text_field_tag :username, params[:username], placeholder: "Search Users", class: "form-control" %>
            <%= text_field_tag :email, params[:email], placeholder: "Search Users", class: "form-control" %>
          <% end %>
        </div>
      </div>

And I've error. How to search => name, username and email?

Thanks

Please show us your error. – Roman Kiselenko Aug 18 '15 at 07:11 — Roman Kiselenko, Aug 18 '15 at 07:11
search isn't working. I dont know how to fix that – Putra Aug 18 '15 at 07:34 — Putra, Aug 18 '15 at 07:34

score 0 · Answer 1 · answered Aug 18 '15 at 07:38

You should not try to escape and sanitize the search params yourself. To write a LIKE query in a safe way use the technic described in this answer). In your example it might look like this:

def search
  User.set_per_page(params[:per_page]) if params[:per_page].present?

  query = User.scope

  [:name, :username, :email].each do |field|
    if params[field].present?
      query = query.where("UPPER(#{field}) LIKE :query", 
                          query: "%#{params[field].upcase}%")
    end
  end

  @users_count = query.count
  @users = query.paginate(:page => params[:page])

  render :index
end

Furthermore you might want to extract that implementation into a .search method on User.

Search multiple text_fields within the same model

1 Answers1