As mentioned in the Introduction, Shine will be sharing a database with an existing customer-facing application. The customer-search feature we’re building will search one of the tables in that database.
In the real world, our database would already exist and you’d hook up to it directly. Since that’s not the case, you’ll need to simulate its existence by creating the table in Shine’s database. And, because you’ll use Postgres query performance optimization, our table is going to need a lot of data in it.
If you were using an existing table, you wouldn’t need a migration—you could just create the Customer model and be done. That’s not the case (since this is an example in a book), so we’ll create the table ourselves. It will ultimately look like the schema.
| The Final Customer Table Schema | ||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
A customer has first and last names, an email address, a username, and creation and last update dates. None of the fields allow null and the data in the username and email fields must both be unique (that’s what the "index_customers_on_email" UNIQUE, btree (email) bit is telling us). This is more or less what we’d expect from a table that stores information about our customers.
Because this table doesn’t exist yet in our example, you can create it using Rails’s model generator. This creates both the database migration that will create this table as well as the Customer class that allows us to access it in our code.
| | $ bundle exec rails g model customer first_name:string \ |
| | last_name:string \ |
| | email:string \ |
| | username:string |
| | invoke active_record |
| | create db/migrate/20160718151402_create_customers.rb |
| | create app/models/customer.rb |
The migration file Rails created will define our table, column names, and their types, but it won’t include the not null and unique constraints. You can add those easily enough by opening up db/migrate/20160718151402_create_customers.rb.
| | class CreateCustomers < ActiveRecord::Migration[5.0] |
| | def change |
| | create_table :customers do |t| |
| » | t.string :first_name , null: false |
| » | t.string :last_name , null: false |
| » | t.string :email , null: false |
| » | t.string :username , null: false |
| » | |
| » | t.timestamps null: false |
| | end |
| » | add_index :customers, :email, unique: true |
| » | add_index :customers, :username, unique: true |
| | end |
| | end |
With that created, you can go ahead and run the migrations.
| | $ bundle exec rails db:migrate |
| | == 20160718151402 CreateCustomers: migrating =========== |
| | -- create_table(:customers) |
| | -> 0.0257s |
| | -- add_index(:customers, :email, {:unique=>true}) |
| | -> 0.0026s |
| | -- add_index(:customers, :username, {:unique=>true}) |
| | -> 0.0019s |
| | == 20160718151402 CreateCustomers: migrated (0.0303s) == |
You should be able to verify that the schema created matches the schema via
| | > bundle exec rails dbconsole |
| | shine_development=> \d customers |
Next, you need some customer data. Rather than provide you with a download of a giant database dump to install (or include it in the code downloads for this book), I’ll have you generate data algorithmically. We’ll aim to make 350,000 rows of “real-looking” data. To help, we’ll use a gem called faker,[30] which is typically used to create test data. First, add that to the Gemfile:
| | gem 'devise' |
| » | gem 'faker' |
Then, install it:
| | $ bundle install |
| | Installing faker 1.6.5 |
You can now use faker to create real-looking data, which will make it much easier to use Shine in our development environment, since you’ll have real-sounding names and email addresses. We’ll create this data by writing a small script in db/seeds.rb. Rails’s seed data[31] feature is intended to prepopulate a fresh database with reference data, like a list of countries, but it’ll work for creating our sample data.
| | 350_000.times do |i| |
| | Customer.create!( |
| | first_name: Faker::Name.first_name, |
| | last_name: Faker::Name.last_name, |
| | username: "#{Faker::Internet.user_name}#{i}", |
| | email: Faker::Internet.user_name + i.to_s + |
| | "@#{Faker::Internet.domain_name}") |
| | print '.' if i % 1000 == 0 |
| | end |
| | puts |
The reason we’re appending the index to the username and email is to ensure these values are unique. As you added unique constraints to those fields when creating the table, faker would have to have over 350,000 variations, selected with perfect random distribution. Rather than hope that’s the case, you’ll ensure uniqueness with a number. Note the final line in the loop where you use print if the current index is a multiple of 1000. This will output a dot on the command line to give you a sense of progress. With the seed file created, we’ll run it (this may take up to 30 minutes depending on the power of your computer).
| | $ bundle exec rails db:seed |
With data filling the table, you can implement the basics of the search feature.
When starting a new feature, it’s best to start from the user interface, especially if you didn’t have a designer design it in advance. Recall that our requirements are to allow searching by first name, last name, and email address. Rather than require users to specify which field they’re searching by, we’ll provide one search box and do an inclusive search of all fields on the back end. We’ll also display the results in a table, since that is fairly typical for search results.
The search will be implemented as the index action on the customers resource. First, add a route to config/routes.rb:
| | root to: "dashboard#index" |
| » | resources :customers, only: [ :index ] |
That route expects an index method on the CustomersController class, which doesn’t exist yet. You could use a Rails generator, but it’s just as easy to create the file from scratch. You’ll create app/controllers/customers_controller.rb and add the definition of the CustomersController there. You’ll implement the index method to just grab the first ten customers in the database, so you have some data you can use to style the view.
| | class CustomersController < ApplicationController |
| | def index |
| | @customers = Customer.all.limit(10) |
| | end |
| | end |
You can now start building the view in app/views/customers/index.html.erb (which won’t exist yet). You’ll need two main sections in your view: a search form and a results table. First, you should make a header letting users know what page they’re on.
| | <header> |
| | <h1 class="h2">Customer Search</h1> |
| | </header> |
Next, create the search form. Because there’s just going to be one field, you don’t need an explicit label (though you’ll include markup for one that’s only visible to screen readers). The design we want is a single row with both the field and the submit button, filling the horizontal width of the container (a large field will feel inviting and easy to use).
Bootstrap provides CSS for a component called an input group. An input group allows you to attach elements to form fields. We’ll use it to attach the submit button to the right side of the text field. This, along with the fact that Bootstrap styles input tags to have a width of 100%, will give us what we want.
| | <section class="search-form"> |
| | <%= form_for :customers, method: :get do |f| %> |
| | <div class="input-group input-group-lg"> |
| | <%= label_tag :keywords, nil, class: "sr-only" %> |
| | <%= text_field_tag :keywords, nil, |
| | placeholder: "First Name, Last Name, or Email Address", |
| | class: "form-control input-lg" %> |
| | <span class="input-group-btn"> |
| | <%= submit_tag "Find Customers", |
| | class: "btn btn-primary btn-lg" %> |
| | </span> |
| | </div> |
| | <% end %> |
| | </section> |
The sr-only class on our label is provided by Bootstrap and means “Screen Reader Only.” You should use this on elements that are semantically required (like form labels) but that, for aesthetic purposes, you don’t want to be visible. This makes your UI as inclusive as possible to users on all sorts of devices.
With our search form styled (you’ll see what it looks like in a moment), we’ll now create a simple table for the results. Applying the class table to any table causes Bootstrap to style it appropriately for the table’s contents. Adding the class table-striped will create a striped effect where every other row has a light gray background. This helps users visually navigate a table with many rows.
| | <section class="search-results"> |
| | <header> |
| | <h1 class="h3">Results</h1> |
| | </header> |
| | <table class="table table-striped"> |
| | <thead> |
| | <tr> |
| | <th>First Name</th> |
| | <th>Last Name</th> |
| | <th>Email</th> |
| | <th>Joined</th> |
| | </tr> |
| | </thead> |
| | <tbody> |
| | <% @customers.each do |customer| %> |
| | <tr> |
| | <td><%= customer.first_name %></td> |
| | <td><%= customer.last_name %></td> |
| | <td><%= customer.email %></td> |
| | <td><%= l customer.created_at.to_date %></td> |
| | </tr> |
| | <% end %> |
| | </tbody> |
| | </table> |
| | </section> |
Now that the table is styled, you can see the entire view in the following figure. It looks pretty good, and you still haven’t had to write any actual CSS.
With the UI built, all you need to do to implement our search is replace the implementation of index in CustomersController with the actual search.
At a high level, our search should accept a string and do the right thing. Because our users are interacting with customers via email, we want to search by email, but because customers sometimes use multiple email addresses, we also want to search by first name and last name. To more strictly state our requirements:
If the search term contains a “@” character, search email by that term.
Use the name part of the email to search first and last name (for example, we’d search for “pat” if given the term “pat123@example.com”).
If the search term does not contain an “@” character, don’t search by email, but do search by first and last name.
The search should be case-insensitive.
The first and last name search should match names that start with the search term, so a search for “Pat” should match “Patty.”
The results should be ordered so that exact email matches are listed first, and all other matches are sorted by last name.
This isn’t the most amazing search algorithm, but it’s sufficient for our purposes here, which is to implement the feature and demonstrate the performance problems present in an even moderately complex query.
There are two tricky things about the search we’re running. The first is that we want case-insensitive matches, and Active Record has no API to do that directly. The second is that we want exact email matches first. Fortunately, Postgres provides a means to do both of these things. You can use SQL like lower(first_name) LIKE ’pat%’ and you can use complex expressions in the order by clause. Ultimately, you’ll want a query that looks like this:
| | SELECT |
| | * |
| | FROM |
| | customers |
| | WHERE |
| | lower(first_name) LIKE 'pat%' OR |
| | lower(last_name) LIKE 'pat%' OR |
| | lower(email) = 'pat@example.com' |
| | ORDER BY |
| | email = 'pat@example.com' DESC, |
| | last_name ASC |
The order by in Postgres can take a wide variety of expressions. According to documentation, it can “be any expression that would be valid in the query’s select list.”[32] In our case, we can order fields based on the results of matching the email column value to pat@example.com. This evaluates to true or false for each row.
Note that semantically, we could use an expression such as first_name ILIKE ’pat%’, which uses a case-insensitive LIKE. This will not use the index we’ll create later and can’t be as easily optimized. As you learn more about indexing and databases, you’ll find that you need to write SQL in a particular way to trigger the use of an index. This is a great example of that.
Because Postgres considers false less than true, an ascending sort would sort rows that don’t match pat@example.com first, so we use desc to sort email matches first.
To execute this query using Active Record, you’d need to write the following code:
| | Customer.where("lower(first_name) LIKE :first_name OR " + |
| | "lower(last_name) LIKE :last_name OR " + |
| | "lower(email) = :email", { |
| | first_name: "pat%", |
| | last_name: "pat%", |
| | email: "pat@example.com" |
| | }).order("email = 'pat@example.com' desc, last_name asc") |
Note that you’re appending % to the name search term and using like so that you meet the “starts with” requirement. You have to do this because Active Record has no direct API for doing this. To create this query in our code, let’s create a class called CustomerSearchTerm that can parse params[:keywords] and produce the arguments you need to where and order.
Our class will expose three attributes: where_clause, where_args, and order. These values will be different depending on the type of search being done. If the user’s search term included an @, we’ll want to search the email column, in addition to last_name and first_name. If there’s no @, we’ll just search first_name and last_name.
First, let’s create app/models/customer_search_term.rb and add the three attributes as well as an initializer. Let’s assume that two private methods exist called build_for_email_search and build_for_name_search that will set the attributes appropriately, depending on the type of search as dictated by the search term. We’ll see their implementation in a minute, but here’s how we’ll use them in the constructor of CustomerSearchTerm:
| | 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 |
We’re converting our term to lowercase first, so that we don’t have to do it later, and we’re also initializing @where_clause and @where_args under the assumption that they will be modified by our private methods.
Let’s implement build_for_name_search first. We’ll create a helper method case_insensitive_search that will construct the SQL fragment we need and use that to build up @where_clause inside build_for_name_search. We’ll also create a helper method called starts_with that handles appending the % to our search term.
| | private |
| | |
| | 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 |
Next, we’ll implement build_for_email_search, which is slightly more complex. Given a search term of “pat123@example.com” you want to use that exact term for the email part of our search. But because we want rows where first_name or last_name starts with just “pat” we’ll create a helper method called extract_name that uses regular expressions in gsub to remove everything after the @ as well as any digits.
| | def extract_name(email) |
| | email.gsub(/@.*$/,'').gsub(/[0-9]+/,'') |
| | end |
There’s one last bit of complication, which is the ordering. To create the order by clause we want, it may seem you’d have to do something like this:
| | @order = "email = '#{search_term}' desc, last_name asc" |
If building a SQL string like this concerns you, it should. Because search_term contains data provided by the user, it could create an attack vector via SQL injection.[33] To prevent this, you need to SQL-escape search_term before you send it to Postgres for querying. Active Record provides a method quote, available on ActiveRecord::Base’s connection object.
Armed with this knowledge, as well as our helper method extract_name_from_email, you can now implement build_for_email_search.
| | def build_for_email_search(search_term) |
| | @where_clause << case_insensitive_search(:first_name) |
| | @where_args[:first_name] = starts_with(extract_name(search_term)) |
| | |
| | @where_clause << " OR #{case_insensitive_search(:last_name)}" |
| | @where_args[:last_name] = starts_with(extract_name(search_term)) |
| | |
| | @where_clause << " OR #{case_insensitive_search(:email)}" |
| | @where_args[:email] = search_term |
| | |
| | @order = "lower(email) = " + |
| | ActiveRecord::Base.connection.quote(search_term) + |
| | " desc, last_name asc" |
| | end |
Note that you don’t need to use quote when creating your SQL fragment in case_insensitive_search, because the strings involved there are from literals in our code and not user input. Therefore, you know they are safe.
Now that CustomerSearchTerm is implemented, you can use it in CustomersController to implement the search.
| | 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 |
This may have seemed complex, but it’s important to note that this search is quite simplified from what you might actually want. You might really need something more complex; for example, a search term of “Pat Jones” would result in a first name search for “pat” and a last name search for “jones.” The point is that our simplistic search is still too complex for Active Record’s API to handle. You had to create your own where clause and your own order by.
Now that our search is implemented, you can see the results by starting our server (via forman start) and navigating to http://localhost:5000/customers. As you see in the following figure, the email match is listed first, while the remaining ones are sorted by last name.
Depending on the computer you’re using, the search might seem fast enough. Or it might seem a bit slow. If customers had more rows in it, or our database were under the real stress of production, the search might be unacceptably slow. It’s popular to solve this problem by caching results in a NoSQL[34] database like Elasticsearch.
While there may be a case made for caching, Postgres gives more options than your average SQL database to speed up searches, which means we can get a lot more out of a straightforward implementation before complicating our architecture with additional data stores. In the next section, you’ll learn about the powerful indexing features Postgres provides. You’ll see that they’re much more powerful than the indexes you get from most SQL databases.