Rails高级搜索问题：Active Record SQL查询不准确及语法报错

Let's break down the issues with your current code and fix them step by step:

1. The Root Cause of Inaccurate Gender Results #

Your original gender query uses fuzzy matching with LIKE "%#{params[:gender]}%", which matches any string that contains the input value. Since "female" includes the substring "male", searching for "male" will incorrectly return female users too. For gender (a fixed-value field like "male"/"female"), you need an exact match instead.

2. Fixing the SQL Syntax Error #

Your attempt to write gender: %#{params[:gender]}% mixes up Rails' hash query syntax with SQL LIKE syntax. The correct way to do an exact match is using Rails' hash conditions, and for fuzzy matches (like username), keep the parameterized LIKE query (to avoid SQL injection!).

3. Fixing the Controller Query Chaining #

Right now, your controller overwrites @search each time you add a condition. For example, if both username and gender are present, you'll only apply the gender filter because you reassign @search = User.where(...) instead of chaining the conditions onto the existing query.

Corrected Controller Code #

def index
  if params[:username].present? || params[:gender].present?
    # Start with a base query, then chain filters
    @search = User.all
    @search = @search.where("name LIKE ?", "%#{params[:username]}%") unless params[:username].blank?
    @search = @search.where(gender: params[:gender]) unless params[:gender].blank?
    # Add pagination at the end
    @search = @search.paginate(page: params[:page], per_page: 10)
  else
    @search = User.all.paginate(page: params[:page], per_page: 10)
  end
end

def create
  # Fixed typo: redirect_to instead of "redirect to"
  @search = Search.create(username: params[:username], gender: params[:gender])
  redirect_to @search
end

private

def search_params
  # Corrected parameter name: :username instead of :users
  params.require(:search).permit(:username, :gender)
end

Key Fixes in the Controller: #

• Exact gender match: where(gender: params[:gender]) ensures only users with the exact gender value are returned, no more cross-matching between "male" and "female".
• Proper query chaining: Start with User.all, then add filters with @search = @search.where(...) so both username and gender filters apply when both parameters are present.
• Typo & parameter fixes: Corrected the redirect_to syntax and fixed the search_params to match your actual form parameters.

4. Minor View Improvements #

Your view mostly works, but we can make it more robust and semantically correct:

<h1>Your Search Results</h1>

<% if @search.empty? %>
  <p>No Results Found</p>
<% else %>
  <ul class="users">
    <% @search.each do |user| %>
      <li>
        <%= user.name %> - <%= user.gender %>
      </li>
    <% end %>
  </ul>
<% end %>

<p><%= link_to 'All Users', users_path %></p>

Why This Works: #

• Checking @search.empty? is more accurate for ActiveRecord relations than checking for nil (your original @search will never be nil, just an empty relation).
• Added <li> tags inside the <ul> to follow proper HTML list structure.

Bonus: SQL Injection Safety #

Your username query uses parameterized syntax (LIKE ?, with the value passed separately), which is perfect—it prevents SQL injection. Always stick to this pattern instead of interpolating raw params directly into SQL strings.

内容的提问来源于stack exchange，提问作者Aly Dabbous