Ruby on rails - adding a select tag to search different fields

I'm working on a search feature for an app, I have a a basic search working, however, I need to be able to have a select field to select what the user searches.

i.e If I have a select tag with the following:

Name, Currency, Company name

I need to be able to select the dropdown option and then enter my search term, here is what my form looks like

My form looks like this

<%= form_tag contacts_path, method: :get do %>
  <div class='l-inline-row-block'>
    <div class='l-inline-col'>
      <%= select_tag(:qs, options_for_select(['name', 'customers', 'suppliers', 'tags'], selected: params[:qs])) %>
    </div>

    <div class='l-inline-col'>
      <%= search_field_tag :search, params[:search] %>
    </div>

    <div class='l-inline-col'>
      <%= submit_tag submit_text, { class: 'no_print' } %>
    </div>
  </div>
<% end %>

I have the following in the controller index method

 @contacts = Contact.search(params[:search])

and the following in the model

def self.search(search)
  if search
    contacts = Contact.order(:id)
    contacts = contacts.where("name like ?", "%#{search}%") if search.present?
    contacts
  else
    contacts = Contact.all
  end
end

I have looked into https://railscasts.com/episodes/111-advanced-search-form-revised but I don't need a separate search page I need all the searching to happen on the index page.

Any help would be great.

Update

I've use the following solution from @max (thank you), however, running into some other issues:

Here is the db structure:

  create_table "contacts", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
    t.integer  "customer_account_id"
    t.integer  "supplier_account_id"
    t.string   "name"
    t.string   "salutation"
    t.string   "title"
    t.string   "phone"
    t.string   "mobile"
    t.string   "business_email"
    t.string   "private_email"
    t.date     "date_of_birth"
    t.string   "spouse"
    t.string   "address_1"
    t.string   "address_2"
    t.string   "address_3"
    t.string   "address_4"
    t.string   "postcode"
    t.text     "other_information",   limit: 65535
    t.integer  "created_by"
    t.integer  "updated_by"
    t.string   "contact_type"
    t.integer  "assigned_to"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.string   "company_name"
    t.string   "web_address"
    t.string   "second_phone"
    t.integer  "prospect_strength"
    t.boolean  "obsolete"
    t.string   "url"
    t.index ["obsolete"], name: "index_contacts_on_obsolete", using: :btree
  end

Each contacts record has a contact_type so not sure if we could search off that but need both customer and supplier options.

were using acts_as_taggable for the tags which need to be searchable.

Here is the current method that the previous search uses if this helps

def quick_search_fields
  @quick_search_fields = [
    {
      col_name: 'name',
      title: 'name',
      column_names: ['contacts.name']
    },
    {
      col_name: 'customer_name',
      title: 'customer',
      search_tables: [:customer],
      column_names: ['accounts.name']
    },
    {
      col_name: 'supplier_name',
      title: 'supplier',
      search_tables: [:supplier],
      column_names: ['accounts.name']
    },
    {
      col_name: 'tags',
      title: 'tags',
      tags: true,
      tagged: Contact
    }
  ]
end

Here is my select_tag <%= select_tag(:qs, options_for_select(['name', 'customers', 'suppliers', 'tag_list'], selected: params[:qs])) %> + the changes in max's solution. However, here's the error that I'm getting when searching suppliers, customers and tags.

Mysql2::Error: Unknown column 'contacts.customers' in 'where clause': SELECT  `contacts`.* FROM `contacts` WHERE (`contacts`.`suppliers` LIKE '%john%') ORDER BY id asc LIMIT 20 OFFSET 0

Upvotes: 0

Answers (2)

max

Reputation: 101831

You need to increase the arity of your search method to 2 and construct a LIKE query with a dynamic column name:

class Contact < ApplicationRecord
  # Prevents sql injections
  SEARCHABLE_FIELDS = ['name', 'customers', 'suppliers', 'tags']

  def self.search(field, query)
    if field.present? && query.present? && SEARCHABLE_FIELDS.include?(field)
      # WHERE contacts.field LIKE '%?%'
      where(arel_attribute(field).matches("%#{query}%"))
    else
      all
    end
  end
end

@contacts = Contact.search(params[:qs], params[:search])

If you want to vary the logic performed by the search depending on the field I would really suggest you extract this functionality out of the model into a seperate object or look at gems such as Ransack.

See:

Upvotes: 1

Miklw

Reputation: 333

If I understood what you want to achieve, I think you could do it all in the controller like this :

def index
  if params[:search].present?
    case params[:qs]
    when 'name'
      result = 'your query for name'
    when 'customers'
      result = 'your query for customers'
    when 'suppliers'
      result = 'your query for suppliers'
    else
      result = 'your query for tags'
    end
    result
   else
     a default query if no search
   end
end

Upvotes: 0

Ruby on rails - adding a select tag to search different fields

Answers (2)

Related Questions