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

Question

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

max · Answer 1 · 2021-04-08T08:12:14.863

1

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:

edited Apr 08 '21 at 08:12

answered Apr 07 '21 at 17:24

max

96,212
14
104
165

Rails provides a convenience method for `arel_table[column]` called `arel_attribute` which feels a bit less esoteric IMO.(Also it accepts a `String` or `Symbol`) e.g. `arel_attribute(field).matches("%#{query}%")` – engineersmnky Apr 07 '21 at 18:02
1

I wasn't aware of that. Thanks @engineersmnky – max Apr 07 '21 at 18:07
I’ll give this a try tomorrow thank you, this is the basic search that I’m rebuilding the advance search we have is around 12 different options etc so I hope I can add onto the above. – Ben Bagley Apr 07 '21 at 21:09
@max I'm getting the following error undefined method `includes?' for ["name", "customers", "suppliers", "tags"]:Array Hint: `includes?` is being called on a `Array` object, which might not be the type of object you were expecting. – Ben Bagley Apr 08 '21 at 08:09
Ah sorry should be `.include?`. That one always trips me up as it's so ungrammatical... – max Apr 08 '21 at 08:11
Yeah that trips me up too, I'll make some edit to the question as I'm still running into some errors @max – Ben Bagley Apr 08 '21 at 08:44
Well provided that schema you can't reasonably expect to be able to search all those things without a lot more logic. This simple LIKE query will only work for VARCHAR / text columns in your contacts table. For associations you need to use a different approach using a join. This is really outside the scope of your original question and should be treated as a separate question. – max Apr 08 '21 at 09:20
You also at least have to make sure that the options actually match up with the names of the columns in the table which is not the case. – max Apr 08 '21 at 09:22

score 0 · Answer 2 · edited Apr 07 '21 at 17:03

0

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

edited Apr 07 '21 at 17:03

Dharman

30,962
25
85
135

answered Apr 07 '21 at 16:58

Miklw

173
1
1
6

I’ll give this a try tomorrow thank you, this is the basic search that I’m rebuilding the advance search we have is around 12 different options etc so I hope I can add onto the above. – Ben Bagley Apr 07 '21 at 21:09

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

2 Answers2