Save 36% for Black Friday! Learn more

Ask A Question

Notifications

You’re not receiving notifications from this thread.

How do I implement advanced search?

Ben John Bagley asked in Rails

Hi all,

I'm currently working on re-building a search feature, we have a basic search and an advanced search, I have a basic text search form working for the name, however, I can't seem to get an option based search working.

Here's what I have so far.

Scenario:

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[:qs], params[:search])

and the following in the model

SEARCHABLE_FIELDS = [
  'name',
  'customers_name',
  'suppliers_name',
  'tags'
]

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

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

create_table "accounts", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
  t.string   "short_name",        limit: 9
  t.string   "name"
  t.string   "type"
  t.string   "child_type"
  t.integer  "parent_id"
  t.integer  "position"
  t.datetime "created_at"
  t.datetime "updated_at"
  t.decimal  "value",                       precision: 11, scale: 2, default: "0.0"
  t.boolean  "fixed",                                                default: false
  t.boolean  "allow_new_child",                                      default: false
  t.integer  "created_by"
  t.integer  "updated_by"
  t.boolean  "disabled",                                             default: false
  t.boolean  "locked",                                               default: false
  t.boolean  "restricted",                                           default: false
  t.string   "account_section"
  t.string   "description"
  t.boolean  "is_budget_account",                                    default: true
  t.boolean  "is_budget_enabled",                                    default: false
  t.integer  "sequence"
  t.index ["type"], name: "index_accounts_on_type", using: :btree
end

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

This is the error I'm getting:

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

The advanced search tab looks like this, I'm trying not to use a gem if possible as I want to learn how best to do this, obviously there is a lot of options but I want to focus on the basic option based search for now.

To me though it's a little odd to search the accounts table as that actually doesn't do anything so for now I only want to get the name and tag options working.

Reply
Join the discussion
Create an account Log in

Want to stay up-to-date with Ruby on Rails?

Join 87,110+ developers who get early access to new tutorials, screencasts, articles, and more.

    We care about the protection of your data. Read our Privacy Policy.