ChapterÂ 4.Â Database Finders: Truth or consequences?
Every decision you make has consequences.
In Rails, knowing how to make good decisions can save you both time and effort. In this chapter, weâll look at how user requirements affect the choices you make, right from the very beginning of your app. Should you use scaffolding and modify the generated code? Should you create things from scratch? Either way, when it comes time to customize your app further, you need to learn about finders: getting at your data in a way that makes sense to you and serves your usersâ needs.
Keep fit with the Rubyville Health Club
The Rubyville Health Club prides itself on its ability to find the perfect class for everyone, and recently theyâve launched a new personal trainer service. Demand for the service is high... so high that the trainers are having trouble keeping track of all their clients. The trainers need you to build an application for them, and fast.
The personal trainers need a web application that enables them to quickly and easily manage the workouts for each of their customers. To start with, they need something that lists basic workout details for each client and allows them to add, update and delete records. Hereâs a sketch of the main page:
The scaffolding isnât rightâbut do we write our own code or fix the scaffolding?
The scaffolded application doesnât do exactly what we need. Weâve seen before that itâs easier to create simple applications manually, without using scaffolding at all. But another approach is to create a scaffolded app, and then either change or add to the code Rails generates.
So what should we do here?
The application actually looks pretty close...
There is one part of the generated code that looks kind of similar to the page that the trainers want. The index page lists a set of data that is almost the same as what the trainers have asked for:
Not only does the generated page look similar to what we need, we know that a scaffolded application will give us all of the usual operations on the client workout data. In other words, a scaffolded application will, by default, allow us to create, read, update and delete records.
So, in this case, is it better to fix the scaffold and make the changes we need, or start from scratch, like we did for MeBay?
Weâre going to fix the scaffolding
When we created the MeBay application, we decided not to use scaffolding. The reason for this was that the clients originally wanted something so simple that it was easier to create the application from scratch. They wanted a lot less functionality than scaffolding provides.
This time around, we need access to all of the CRUD operations, plus we need to find the workout sessions for an individual client. As we need more functionality, we can do most of the work using scaffolding as the basis of the application, and then we can add to the generated code.
So how can we find and display the workout sessions for an individual client?
Design the search function
So hereâs what you need to build
A new search page where the trainer will enter the name of the client.
findaction on the controller that will do the actual searching.
A new results page with a list of links to each of the clientâs workoutsâthis will work kind of like the index page already generated by the scaffolding.
So where should we start?
Letâs start by building the form
We have a few new components to create, so letâs start with the user interface. That way weâll be able to get some early feedback from the trainers. Hereâs what the search form trainers will use to find clients should look like:
Youâve built pages with forms before. Can you see anything different about this one?
Take a look at the other forms we just generated for the application, the create and edit forms. They have fields that match the fields of the ClientWorkout model objects. The difference this time is thereâs no model object that matches the search form. So how do we create a form when thereâs no model to base the form on?
The search will need a new kind of form
We need to create a form without using a model object, but the
form_for helper weâve been using requires needs a model object to work. So what do we do?
Fortunately there is another helper tag that creates model-free formsâjust what we need in this situation.
Add the search to the interface
Search is usually a feature, not a separate page.
Most web sites have a search function built into every page, so maybe we could do the same. We could add the search to the top corner of every page, keeping the rest of the content on individual pages intact.
Adding code to every page will mean thereâs a lot of duplicated code to be maintained, but what if we could add the new search code just to a single file?
So are the form parameters structured differently?
form_for helper we used in the previous chapter creates a model formâthat is, an HTML form based upon the attributes of a model object. When a model form is submitted, Rails knows that you are probably going to want to turn the field values back into a model object. For example, when the scaffolded Edit form (which is created with
form_for) is submitted, it structures its parameters like this:
WIth a model form, you can get a hash of all of the field values with a simple expression like
But what about the
form_tag creates an non-model form. Thatâs a form that is used to edit a set of individual field values. For that reason, the search form (which is created with a
form_tag) creates request parameters structured like this:
How do we find client records?
So do we have a problem reading the records for a particular client? When weâve read records so far, weâve done it by returning a single record, or by finding all the records in a table. But whatâs different this time?
Reading a single record
We can read a single record using the value in the id column. We know that this technique returns just one record because the id number is unique for each record.
Reading all the records
If instead of passing the id number, we pass the special symbol
:all;then the model will return an array with all of the records in the underlying table.
Reading records that match a specific criteria
This time, we want to read records that match a specific search criteria. We may want to return more than one record, so we need something that will return an array of model objects. But we donât want a model object for every recordâonly those that match the search criteria.
We only need those records where client-name = the search string
The trainers want to search for all of the workouts for a particular customer. The model will need a simple test that will be true for matching records and false for the rest. Something like this:
If the model can apply that test to each of the records in the table, it will find all of the matching records in the table:
In general terms, then, we need a finder that can find all records that have a particular value in a particular column in the table.
Thereâs a finder for every attribute
But how? The model code has a finder for each of itâs attributes. You donât need to add these finders yourselfâRails provides them. So the ClientWorkout model has finders for the client name, the workout duration, and so on. And each of these finders returns an array containing all of the matching ClientWorkout objects.
Remember that an attribute in a model object maps to a database column in the underlying table. So each of these finders can be used to find all of the records with a particular value in a particular column.
So whatâs next?
We now have code that will find all of the records that match the search, so now we need to display the results back to the user. But how?
We need to create a
find.html.erb page to display the search results.
We need to match either the client name OR the trainer name
The search works by finding all the records with a particular client name. But if the search will also need to find trainers by name, then the logical test it will apply to each record will need to be a little more complicated. Instead of
client_name = params[:search_string]
the criteria now needs to be:
Can you see a problem here?
Thereâs a finder for each of the attributes in the model object. And each of these finders has a simple test that it applies to the records in the database, checking a single column in the database against a given value. But the test is more complex now, so is there some way to specify the test that the finder applies to the database records?
We need to be able to modify the conditions used in the SQL query
But the conditions in the SQL query are generated by the finder method. We are able to pass in strings to the finder (like âLenny Goldbergâ) but so far we have not done anything to modify the actual structure of the conditions that become part of the SQL sent to the database.
Will being able to modify the SQL query parameters actually be that big a deal? Wellâyes, it will. Finders that look for matching values in particular attributes are usefulâbut specifying the SQL conditions lets you do so much more. It lets you override the default behavior of a finder, and get complete control over the data that is accessed by the model. Thatâs just what we need here, too: more control over the SQL query.
So how do we modify those conditions?
Use :conditions to supply SQL
For that reason, all finders allow you to pass a named parameter called
:conditions containing extra conditions to be added to the SQL that the finder generates.
Hereâs one way in which this could work with the trainer/client search:
This version of the finder will return all of the records that have a trainer or client called âLenny Goldbergâ, but can you see what the problem is? What if we want to search for someone other than Lenny? What we really want is to search for whatever is recorded in the
params[:search_string]. But how?
Fortunately, Rails has a way of doing just that. It allows you to parameterize the conditions like this:
?s in the first string in the conditions array are replaced in sequence with the values that follow. This means that the finder will now be able to generate the correct SQL statement for whatever is in the search parameter. The relevant records are returned for whoever the trainer searches for.
So how well does this work?
Then thereâs a knock at the door...
Just as youâre demonstrating the system thereâs a knock at the door. Itâs some of the guys from the body building club.
It seems like thereâs been a problem with the data entered into the system... get ready for the next chapter, where weâll dig deeper into the bodybuilding problem.
Tools for your Rails Toolbox
Youâve got ChapterÂ 4 under your belt, and now youâve added the ability to choose whether to use scaffolding and how to smartly select the correct data for your application.
find(:all, :conditions=>[...]) allows you to specify the SQL used to select records from the database.
form_tag generates simple forms that are not bound to model objects.