Ruby on Rails: Search Form - Multiple Search Fields

Ruby on Rails: Search Form - multiple search fields

This problem has been solved in another question: Ruby on Rails: Advanced search

Based on your question, @Chris Wise is assuming that you have two columns in your projects table: project_name and client.

I might be wrong, but I think you actually have two models Client and Project, where a client has many projects. If so, you need to define the search in each model:

Client model:

def self.search search_term
  return scoped unless search_term.present? 
  where(['client_name LIKE ?', "%#{search_term}%"]) #client_name means the column name, change it to the correct name.
end

Project model:

def self.search search_term
  return scoped unless search_term.present? 
  where(['project_name LIKE ?', "%#{search_term}%"]) #project_name means the column name, change it to the correct name.
end

Your form:

<%= form_tag projects_path, method: :get do %>
  <%= text_field_tag :project_name, params[:project_name] %>
  <%= text_field_tag :client, params[:client] %>
  <%= submit_tag "Search", name: nil %>
<% end %>

Then your controller:

#return all projects that match the search criteria
@project_search = Project.search(params[:project_name]).all
#return all clients that match the search criteria
@clients_search = Client.search(params[:client]).all

I hope it helps...

Search multiple fields with multiple values

I would do it like this

<h2>Search Referent</h2>
<%= form_tag(referents_path, :method => "get", id: "search-form") do %>
<%= text_field_tag :search_nom, params[:search_nom], placeholder: "Nom" %>
<%= text_field_tag :search_prenom, params[:search_prenom], placeholder: "Prenom" %>
<%= submit_tag "Search", class: 'btn btn-info' %>
<% end %>

Controller

def index
  @referents = Referent.all

  search_nom = params[:search_nom]
  search_prenom = params[:search_prenom]

  @referents = Referent.search(search_nom, search_prenom).order("created_at DESC")
end

Model

def self.search(search_nom, search_prenom)
  where("nom ILIKE ? or prenom ILIKE ?", "%#{search_nom}%", "%#{search_prenom}%")
end

Search multiple fields rails 5

you may need to make view adjustments and you cannot just use the search term/query for all fields:

1.make sure the fields inside the form in your view are all the same names of the columns in the Contact model that need to be searched
2.add a hidden field inside each form so you can determine which search this is (there is another option but this one is less work)
3.change the collection_select where user can only select one field to select_tag in order to not mess up query building

every field that is filled will pass a value- you can build a hash including column/form-field entered and their value e.g { contact_type: "value1", prospect_strength: "value2" }

after that, you would need to build a query using that hash and query the DB

this would look like so:

In your view:

add this in the advanced search form

<%= hidden_field_tag :search_type, :advanced %>

and in your normal search add this

<%= hidden_field_tag :search_type, :normal %>

In your model:

def self.advanced_search(values_by_column_name) # values_by_column_name is a hash passed from the controller
    tag_list = values_by_column_name.delete("tag_list")
    sql_query = values_by_column_name.keys.map { |column| "#{column} LIKE :#{column}" }
    sql_query = sql_query.join(" OR ")
    values_by_column_name = values_by_column_name.transform_values { |value| "%#{value}%" }.symbolize_keys

    relation = Contact.where(sql_query, values_by_column_name)
    relation = relation.tagged_with(tag_list) if tag_list
    relation
end

in your controller:

ADVANCED_SEARCH_FIELDS = [
    :contact_type,
    :prospect_strength,
    :customer_account_id,
    :supplier_account_id,
    :company_name,
    :name,
    :title,
    :postcode,
    :created_at,
    :updated_at,
    {tag_list: []},
    :assigned_to,
    :obsolete,
]

def index
    @per_page = params[:per_page] || 20
    @tags = ActsAsTaggableOn::Tag.all

    if advanced_search?
        @contacts = Contact.advanced_search(advanced_search_params)
    elsif normal_search?
        @contacts = Contact.search(params[:qs], params[:search])
    else
        @contacts = Contact.all
    end

    @contacts = @contacts.order(sort_column + ' ' + sort_direction).paginate(page: params[:page], per_page: @per_page)
end

def advanced_search?
    params[:search_type] == "advanced" && advanced_search_params.any?
end

def normal_search?
    params[:search_type] == "normal" && params[:search].present?
end

def advanced_search_params
    @_advanced_search_params ||= params.permit(ADVANCED_SEARCH_FIELDS).select { |_, v| v.present? }.to_h
end

Can Rails search across multiple columns in one query?

I understand you want to search across multiple fields in your form.
You need to add another field to the form by which you want to search, and you must process the result of the response in the controller action.

@tasks = Task.where('name=? OR some_another_field=?', params[:name], params[:some_another_fields])

Multiple search fields in rails 4

It looks like the problem is in the function def self.actives(active)

Try changing it to

 def self.actives(active)
    user = all
    user = user.where("active = ?", active)
    return user
end

You might also want to try the Ransack gem in order to easily add/remove fields at a search form
https://github.com/activerecord-hackery/ransack

Search query multiple column with OR and AND to apply filter

Just use parentheses around the or conditions, the result of the or conditions will then be combined with the and conditions.

left_outer_joins(:caracteristiquetests, :situations).where(['(nomdep LIKE ? OR name LIKE ? OR nomregion LIKE ?) AND handicap LIKE ? AND animaux LIKE ? AND television LIKE ? AND plage LIKE ? AND etang LIKE ? AND lac LIKE ?', "%#{query}%", "%#{query}%", "%#{query}%", "%#{handicap}%", "%#{animaux}%", "%#{television}%", "%#{plage}%", "%#{etang}%", "%#{lac}%"])

Alternatively, you can split it up into separate where statements, which will combine to make one SQL call when you retrieve the results. This would be easier to read and maintain, and doesn't do any calls where a value wasn't provided.

result = left_outer_joins(:caracteristiquetests, :situations).where('nomdep LIKE ? OR name LIKE ? OR nomregion LIKE ?', "%#{query}%", "%#{query}%", "%#{query}%")
result = result.where('handicap LIKE ?', "%#{handicap}%") if handicap
result = result.where('animaux LIKE ?', "%#{animaux}%") if animaux
result = result.where('television LIKE ?', "%#{television}%") if television
result = result.where('plage LIKE ?', "%#{plage}%") if plage
result = result.where('etang LIKE ?', "%#{etang}%") if etange
result = result.where('lac LIKE ?', "%#{lac}%") if lac
return result

Multiple search fields in rails

Do it by using scopes...

  scope :bedrooms, lambda{ |b| where("bedrooms LIKE ?", b) }    
  scope :price_greater, lambda{ |p|  where("price > ?", p)  }

in controller

  @properties = Property.scoped
  @properties = @properties.bedrooms(params[:bedrooms]) if params[:bedrooms].present?
  @properties = @properties.price_greater(params[:min]) if params[:min].present?
  .....
  @properties = @properties.paginate.... or just @properties.all

Search multiple columns - Rails

If I understand your question correctly, your SQL looks good to me for what you are trying to do. An OR clause will return all records that match in column1, column2, or column3. It doesn't stop at the first match. I do see an issue with your parameters in that the first you are using LIKE with % but in the second two you aren't, maybe that is where your issue is coming from.

Should this be your find (% around second and third search)?

find(:all, :conditions => ['game_name LIKE ? OR genre LIKE ? OR console LIKE ?', "%#{search}%", "%#{search}%", "%#{search}%"])

or better use DRY version (above will not work for Rails 4.2+):

Item.where('game_name LIKE :search OR genre LIKE :search OR console LIKE :search', search: "%#{search}%")

---

Related Topics