All threads / How do I implement advanced search?
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.

Join the discussion

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

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

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

    logo Created with Sketch.

    Screencast tutorials to help you learn Ruby on Rails, Javascript, Hotwire, Turbo, Stimulus.js, PostgreSQL, MySQL, Ubuntu, and more. Icons by Icons8

    © 2023 GoRails, LLC. All rights reserved.