When Rory starts to write a new search view, he soon realizes that he is repeating the same code that he has in his index view; that is a list of records. The difference is that in the index view all records are being shown, whereas in the search view, a subset of all the records is being shown. If he renders the index view, but passes it the result of the search call, the coding will be greatly simplified.

If the index view is rendered when processing the search action, the index view needs to contain the search form; that is the form where the users submit their search criteria. This is often a good thing, rather than being a problem. That is, users are presented with a search option each time they access an index view. They can start querying and sampling the index list without having to move to a separate view. However, to achieve this, the search form needs to be small and simple.

It is a good idea to provide immediate user access to a quick and simple search tool. Often, this is the only search option required. However, if more advanced options are required, we can always add a second advanced search action.

Rory's first attempt at a search function is not satisfactory and inherently insecure. However, it makes a good starting point for describing how to add search functionality. it also demonstrate some problems that can occur.

To create a search function, Rory initially adds a search form by altering the start of views/companies/index.rhtml to:

<h1><%= @page_title %></h1>
<p><%= form_tag({:controller => 'companies',
:action => 'search'},
{:method => :get}) %>
<label for="term">Find company with name:</label>
<%= text_field_tag "term" -%>
<%= submit_tag 'Go' %>
</form></p><br />

He then adds the following method to the companies' controller:

def search
name = params[:term]
@paginator, @companies = paginate :company,
:per_page => 10,
:order => 'name',
:conditions => "name = '#{name}'"
@page_title = "List of companies called #{name}"
render :action => 'index'
end

He uses @page_title to create a page title that is specific to the search. This is useful when navigating back and forth through the browser history, as each search will have a different title and therefore will be more easily identified.

When Rory enters a company name into the search textbox and clicks on the Go button, the entry for that company is listed and he can navigate to the show or edit view from there. However, as he starts trying a few options he discovers a problem. He enters a company name that includes an apostrophe and an error is generated. When he investigates this further he finds there is something missing from his current code.

The problem with Rory's code is that it relies on the user entering a correctly formatted search term, and not entering potentially damaging code. Consider these two examples:

Fortunately there is a Rails construct that will deal with both issues. Rails will run a number of processes on an entry including removing any potentially harmful code, if we pass :conditions an array formatted as follows:

Therefore, to use this technique in Rory's code, he would change the conditions statement. So the controller code became:

def search
name = params[:term]
@paginator, @companies = paginate :company,
:per_page => 10,
:order => 'name',
:conditions => ["name = ?", name]
@page_title = "List of companies called #{name}"
render :action => 'index'
end

Besides dealing with errors in a user's input, this technique also handles different data types. For example, rather than having to reformat dates into the default SQL format (YYYY-MM-DD) before using them in a query, Rails will do that for us. So to restrict the returned records to only those created on a given date, we would use:

:conditions => ["created_on = ?", date]

We can also have a number of variables within each statement. In this case, Element 2 will either need to be replaced with a list of the substitutes, or with an array containing the variables that need to be substituted into the first element. Either way, the set of substitutes must be in the order they are to be substituted into the first element. For example, if we wanted to only return companies created in the last ten days, whose names were "Bloggins", we could use either:

:conditions => ["name = ? and created_at > ?", name, 10.days.ago]

...or create an array:

condition_elements = [name, 10.days.ago]

...and use that in the :conditions statement:

:conditions => ["name = ? and created_at > ?", condition_elements]

There are two conditions that need to be handled. There are no companies in the database, and/or no companies are returned by the current search.

Between the end of the search form and the start of the table displaying the list of companies, Rory can add a conditional statement. If there is no data returned from the query, the statement will return a message to the user instead of an empty table.

<% unless @companies.empty? -%>
<h2> No companies match your criteria </h2>
<% else -%>

...and to the end of the code, add an end to close the if statement as follows:

<% end -%>

He could also add code to differentiate between there being nothing to show because a search has failed, or because there is no data in the database.

With Rory's code as it is, the user needs to know the name of the company they are searching for to get a successful result to their search. For example, if a company name was "Biggum", and a user searched for "Bigum" the company would not be located with this search. As it will be common for users not to know the exact name or spelling of a company name, we need to provide them with another way to search. There are two approaches to dealing with this problem:

The second approach is a more complicated process. In the first, all that is required is a small change to the way the database is queried. Whereas in the second approach, a system has to be put in place to continually monitor a user's input and update a list showing matching companies as new characters are added to the search string. However, in Rails, the second approach is actually simpler to create, because a number of helpers have been created that automate the process. The automated process relies on AJAX which deserves a section on its own. So rather than introducing it here, I will initially describe the first approach, and then use the second approach as an introduction to using AJAX with Rails.

SQL provides a way of constructing a conditional WHERE statement so that instead of looking for an exact match, the system returns any rows where the field being queried simply contains the string being searched for. This only works with character string fields, but can be a very useful technique to use with text searches.

When we use a Class.find method in Rails, ActiveRecord builds a SQL statement from our code input, and uses that to query the database. As we have already seen, ActiveRecord uses the contents of the conditions element to construct the WHERE clause of the SQL query. So when searching for companies called "Biggum" Rory's code:

:conditions => ["name = ?", name]

...would be translated to:

WHERE name = 'Biggum'

The WHERE clause has the format:

WHERE field_name = search_string

The alternative approach replaces the equals operator (=) with the operator LIKE. If LIKE is used on its own, it simply replaces the equals operator when comparing string character fields. However, when combined with the % operator, it becomes more flexible as the % becomes a wild-card entry. So:

So if Rory changes his code to use the format:

WHERE field_name LIKE '%search_string%',

...users entering new search stings will only need to know that the company name contains a certain sequence of characters. It then becomes a fairly simple task for most users to find a sequence of characters that will return a search result that contains the company name they are looking for.

To achieve this, Rory could simply replace the conditions entry with:

:conditions => "name LIKE '%#{name}%'"

However, as discussed above, by so doing Rory would expose his application to problems caused by users' incorrectly formatted entries. Instead a better approach is to use:

:conditions => ["name LIKE ?", "%#{name}%"]

Note that the % operators have to be added to the right-hand-side of the statement, otherwise ActiveRecord will insert a set of apostrophes inside the % operators.

As we may want to use LIKE in many places within our application, the best DRY practice is to add a method to prepare text for use in a LIKE statement. Rory adds the following to application.rb:

protected
# Adds apostrophe to text for SQL LIKE statement.
# prep_for_like('search') > '%search%'
# prep_for_like('search', 'start') > 'search%'
# prep_for_like('search', 'match') > 'search'
def prep_for_like(text, placement='contain')
case placement
when 'match'
text
when 'start'
"#{text}%"
when 'end'
"%#{text}"
else
"%#{text}%"
end
end

Rory could then update the conditions statement to:

:conditions => ["name LIKE ?", prep_for_like(name)]

With MySQL, the WHERE statements are case insensitive in the default configuration. That is the statement WHERE field = 'HELLO' will find all of these variations: Hello; HELLO; hello; or even heLLo. However, many databases are case sensitive. Even with MySQL, an administrator can turn off case insensitivity.

If you find your database is case sensitive, you will need to adjust your search code so that users do not have to match the case that a string is stored in within the database. The easiest way to do this is to force both the query string and the text being compared, into the same case— either upper or lower. In fact, it is a good practice to use this technique for all search queries in case your application is moved to a database that is case sensitive.

In SQL the function LCASE converts a string to lower case. Therefore, the following variation of the conditions statement forces SQL to do a case insensitive comparison.

:conditions => ["LCASE(name) like LCASE(?)", prep_for_like(name)]