Search multiple fields rails 5

Question

I have the following form:


  <%= form_tag contacts_path, method: :get do %>
    
      
        
          <%= label_tag "Contact Type" %>
          <%= select_tag(:contact_type, options_for_select(Contact::TYPES, selected: params[:contact_type]), prompt: 'Any', class: 'customSelect') %>
        

        
          <%= label_tag "Prospect Strength" %>
          <%= select_tag(:prospect_strength, options_for_select(Contact::PROSPECT_STRENGTHS, selected: params[:prospect_strength]), prompt: 'Any', class: 'customSelect') %>
        

        
          <%= label_tag "Customer" %>
          <%= collection_select(:customer_account_id, :customer_account_id, Customer.order(:name), :id, :name, { prompt: '' }, class: 'customSelect select2') %>
        

        
          <%= label_tag "Supplier" %>
          <%= collection_select(:supplier_account_id, :supplier_account_id, Supplier.order(:name), :id, :name, { prompt: '' }, class: 'customSelect select2') %>
        

        
          <%= label_tag "Company Name" %>
          <%= text_field_tag :company_name %>
        

        
          <%= label_tag "Name" %>
          <%= text_field_tag :name %>
        

        
          <%= label_tag "Job Title" %>
          <%= text_field_tag :title %>
        

        
          <%= label_tag "Postcode" %>
          <%= text_field_tag :postcode %>
        

        
          <%= label_tag :created_at, 'Created From' %>

          
            <%= text_field_tag :created_at, nil, class: 'date-picker-select' %>

            
              
            
          
        

        
          <%= label_tag :updated_at, 'Created To' %>

          
            <%= text_field_tag :updated_at, nil, class: 'date-picker-select' %>

            
              
            
          
        

        
          <%= label_tag "Tags" %>
          <%= collection_select(:tag_list, :tag_list, @tags.order(:name), :name, :name, {}, { class: 'select2', multiple: true }) %>
        

        
          <%= label_tag "Assignee" %>
          <%= collection_select(:assigned_to, :assigned_to, User.all, :id, :name, { prompt: 'Any' }, class: 'customSelect select2') %>
        

        
          <%= label_tag "Obsolete?" %>
          <%= select_tag(:obsolete, options_for_select(['Obsolete', 'All'], selected: params[:obsolete]), prompt: 'Not obsolete?', class: 'customSelect') %>
        

        
          <%= label_tag "Send Results To" %>
          <%= select_tag(:subsequent_action, options_for_select([
            ['Report', 'report'],
            ['CSV Export', 'csv_export'],
            ['New Event', 'new_event']
          ]), prompt: 'None', class: 'customSelect') %>
        
      

      
        <%= submit_tag submit_text %>
        <%= link_to secondary_btn, contacts_path, class: 'btn-medium' %>
      
    
  <% end %>

and the following method in the model

def self.advanced_search
  Contact.where('
    contact_type LIKE :search OR
    prospect_strength LIKE :search OR
    customer_account_id LIKE :search OR
    supplier_account_id LIKE :search OR
    company_name LIKE :search OR
    name LIKE :search OR
    title LIKE :search OR
    postcode LIKE :search OR
    created_at LIKE :search OR
    updated_at LIKE :search OR
    tag_list LIKE :search OR
    assigned_to LIKE :search OR
    obsolete LIKE :search
  ', search: "%#{search}%"
  )
end

How do I go about using this method so the user can use this search form with multiple params? I already have the following in the index method for a basic search so I need to have both search forms

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

  if params[:search].present?
    @contacts = Contact.search(params[:qs], params[:search]).order(sort_column + ' ' + sort_direction).paginate(page: params[:page], per_page: @per_page)
  else
    @contacts = Contact.all.order(sort_column + ' ' + sort_direction).paginate(page: params[:page], per_page: @per_page)
  end
end

Edit Updated the form above to the complete form, ideally, I would like the forms functionality to be entirely in the model.

Edit #2

This is the basic search:

model

QUICK_SEARCH_FIELDS = {
  name: {
    column_names: 'contacts.name'
  },
  customers: {
    joins_table: :customer,
    column_names: 'contacts.name',
    filters: { contact_type: 'customer' }
  },
  suppliers: {
    joins_table: :supplier,
    column_names: 'contacts.name',
    filters: { contact_type: 'supplier' }
  },
  tags: {
    tagged_with: true
  }
}.with_indifferent_access

def self.search(field, query)
  field = QUICK_SEARCH_FIELDS[field]

  contact = all
  contact = contact.joins(field[:joins_table]) if field[:joins_table]
  contact = contact.where(field[:filters]) if field[:filters]
  contact = contact.where("#{field[:column_names]} LIKE ?", "%#{query}%") if field[:column_names]
  contact = contact.tagged_with(query) if field[:tagged_with]
  contact
end

form


  <%= form_tag contacts_path, method: :get do %>
    
      
        <%= select_tag(:qs, options_for_select(Contact::QUICK_SEARCH_FIELDS.keys(), selected: params[:qs]), class: 'customSelect') %>
      

      
        <%= search_field_tag :search, params[:search] %>
      

      
        <%= submit_tag submit_text %>
      
    
  <% end %>

Mshka · Accepted Answer

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

Search multiple fields rails 5

Answers (1)

In your view:

In your model:

in your controller:

Related Questions