One great way to implement a basic search is to put all your data fields into one big array and then perform the search on this one field. Let’s say, you’ve got a Customer model. Open up customer.rb (in app/models/) and write this few lines of code:
class Blueprint < ActiveRecord::Base
scope :search, ->(search){ where('keywords LIKE ?', "%#{search.downcase}%") }
before_save :set_keywords
protected
def set_keywords
self.keywords = [name, email, birthdate, etc.].map(&:downcase).join(' ')
end
end
To pick only the searched items we’re creating a scope to which we’re passing the search argument. You can add a function, which will turn all separate fields into one big keywords array.
Now, open up your Controller (CustomersController.rb)
class CustomersController < ApplicationController @customers = Customer.search(params[:search]) end
If desired, you can add some validation for cases if no search parameter is provided.
This approach won’t yield you amazon like results, but can bring one pretty far.
Step up the game
If you want to make one little step further and do not want to bother with „heavy industry“ tools like ElasticSearch, then you can utilize PostgreSQL’s awesome built in indexing features. This approach requires a bit more setup, than the first one, but once you follow those steps, it will appear straight forward.
The main goal is to write our custom SQL query. In the end what we want to achieve is something like this:
SELECT * FROM customers WHERE lower(first_name) LIKE 'bob%' OR lower(last_name) LIKE 'bob%' OR lower(email) LIKE 'bob@example.com' ORDER BY email = 'bob@example.com' DESC, last_name ASC
Using rails syntax this would mean we need a query like this:
Customer.where("lower(first_name) LIKE :first_name OR " +
"lower(last_name) LIKE :last_name OR " +
"lower(email) = :email", {
first_name: "bob%",
last_name: "bob%",
email: "bob@example.com"
}).order("email = 'bob@example.com' desc, last_name asc")
To create this query and keep things clean I’m using an extra class (models/customer_search_term.rb):
class CustomerSearchTerm
attr_reader :where_clause, :where_args, :order
def initialize(search_term)
search_term - search_term.downcase
@where_clause = ""
@where_args = {}
if search_term =~ /@/
build_for_email_search(search_term)
else
build_for_name_search(search_term)
end
end
#Following code goes there
end
Now we need this helper function, „build_for_name_search“ (I’ll omit the first one for now).
def build_for_name_search(search_term)
@where_clause << case_insensitive_search(:first_name)
@where_args[:first_name] = starts_with(search_term)
@where_clause << " OR #{case_insensitive_search(:last_name)}"
@where_args[:last_name] = starts_with(search_term)
@order = "last_name asc"
end
def starts_with(search_term)
search_term + "%"
end
def case_insensitive_search(field_name)
"lower(#{field_name}) like :#{field_name}"
end
This function does nothing else rather than building the string for our SQL query. You can verify it by examining the variables – @where_clause is a string, while @where_args is a hash.
Finally, let’s build our controller:
class CustomersController < ApplicationController def index if params[:keywords].present? @keywords = params[:keywords] customer_search_term = CustomerSearchTerm.new(@keywords) @customers = Customer.where( customer_search_term.where_clause, customer_search_term.where_args). order(customer_search_term.order) else @customers = [] end end end
We’re almost there. This search works, but it’s still a bit slow (see below for speed results).
Now we need to create custom indexes on those tables. We will stick to old up and down migration methods, since rails won’t understand our custom SQL migration if we’d stick to „change“.
class AddLowerIndexesToCustomers < ActiveRecord::Migration
def up
execute %{
CREATE INDEX
customers_lower_last_name
ON
customers (lower(last_name) varchar_pattern_ops)
}
execute %{
CREATE INDEX
customers_lower_first_name
ON
customers (lower(first_name) varchar_pattern_ops)
}
execute %{
CREATE INDEX
customers_lower_email
ON
customers (lower(email))
}
end
def down
remove_index :customers, name: 'customers_lower_last_name'
remove_index :customers, name: 'customers_lower_first_name'
remove_index :customers, name: 'customers_lower_email'
end
end
It’s important to use „varchar_pattern_ops“, since we are not using an exact match, but instead using a like operator. Now, if we fire up rails dbconsole and perform EXPLAIN ANALYZE on both methods, we can see the difference.Ama
This was the original result:
Started GET "/customers?utf8=%E2%9C%93&keywords=andres148127%40dicki.biz&commit=Find+Customers" for ::1 at 2017-01-04 22:19:21 +0300
Processing by CustomersController#index as HTML
Parameters: {"utf8"=>"✓", "keywords"=>"andres148127@dicki.biz", "commit"=>"Find Customers"}
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT 1 [["id", 1]]
Customer Load (561.3ms) SELECT "customers".* FROM "customers" WHERE (lower(first_name) like 'andres%' OR lower(last_name) like 'andres%' OR lower(email) like 'andres148127@dicki.biz') ORDER BY lower(email) = 'andres148127@dicki.biz' desc, last_name asc
Rendered customers/index.html.haml within layouts/application (572.4ms)
Completed 200 OK in 591ms (Views: 27.8ms | ActiveRecord: 561.6ms)
After spicing things up:
Started GET "/customers?utf8=%E2%9C%93&keywords=andres148127%40dicki.biz&commit=Find+Customers" for ::1 at 2017-01-04 22:40:59 +0300
Processing by CustomersController#index as HTML
Parameters: {"utf8"=>"✓", "keywords"=>"andres148127@dicki.biz", "commit"=>"Find Customers"}
User Load (0.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT 1 [["id", 1]]
Customer Load (2.1ms) SELECT "customers".* FROM "customers" WHERE (lower(first_name) like 'andres%' OR lower(last_name) like 'andres%' OR lower(email) like 'andres148127@dicki.biz') ORDER BY lower(email) = 'andres148127@dicki.biz' desc, last_name asc
Rendered customers/index.html.haml within layouts/application (12.7ms)
Completed 200 OK in 32ms (Views: 28.3ms | ActiveRecord: 2.3ms)
This is an increase by a factor of 243!