4. Database Finders: Truth or consequences?

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:

Let’s start the application by scaffolding a set of pages for this model:

client_workouts

`Column`	`Type`
`client_name`	`string`
`trainer`	`string`
`duration_mins`	`integer`
`date_of_workout`	`date`
`paid_amount`	`decimal`

What should the scaffold command be? Write your answer below. Go ahead and run this command, too:

________________________________________________________________________________________

Now, look again at what the trainers want to do with the application, and write down any differences between what the application needs to do and what the application actually does based on the scaffolded version you just created:

Note

Write your answer here.

________________________________________________________________________________________

Let’s start the application by scaffolding a set of pages for this model:

client_workouts

`Column`	`Type`
`client_name`	`string`
`trainer`	`string`
`duration_mins`	`integer`
`date_of_workout`	`date`
`paid_amount`	`decimal`

What should the scaffold command be? Write your answer below.

Note

Remember that you would need to create a new Rails application before running this command...

ruby script/generate scaffold client_workout client_name:string trainer:string duration_mins:integer date_of_workout:date paid_amount:decimal

Note

...and then “rake db:migrate” afterwards to create the tables.

Your job was to figure out the differences between what the application needs to do and what the scaffolded application actually does.

The trainers want to look at the workout sessions that have been recorded for each client.

The scaffolded application will only show workouts for a single client, or all of the clients together.

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

Here’s what the search function should look like:

So here’s what you need to build

A new search page where the trainer will enter the name of the client.
A find action 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.

You’ll need to create a search page template. Trouble is— after working out the main part of the code on the fridge door somebody slammed the door and the magnets all fell off!

Fortunately the code for a non-model form is pretty similar to a model form.

Can you figure out what the code should look like?

<%____________________________%>
<%=________________________%>
<%=__________________%>
<%_______%>

Q:
Q: I don’t get why we can’t just use scaffolding on its own. Doesn’t that give us everything?
A:
A: Scaffolding only provides basic Create, Read, Update, and Delete operations. Most applications will need functions that go beyond basic CRUD functionality.
Q:
Q: Why didn’t we use scaffolding for the MeBay application?
A:
A: We didn’t use scaffolding for MeBay because we originally only needed a basic read-only application. For really simple applications it can be easier and more efficient to create your application manually.
Q:
Q: Could we still have generated the application using scaffolding, and then removed the operations we didn’t want?
A:
A: Yes, we could have done that. You will probably start most applications using scaffolding. Only in cases where you need very little functionality, or where the functionality is very different from scaffolding, will you want to create an app manually.
Q:
Q: So sometimes it’s best to start from scratch, and other times it’s best to fix the scaffolding. Which option should I use when? How can I decide what’s best?
A:
A: Go for scaffolding if you’re going to use most of the CRUD operations, create, read, update, and delete. Ask yourself: which will be quicker—creating the code manually, or stripping out the unused scaffolding code?
Q:
Q: What is a model form?
A:
A: A model form is a form bound to a model object. When that sort of form is displayed, the field values will come from the attributes of a model object.
Q:
Q: And a non-model form?
A:
A: That’s a form that isn’t bound to a model object. A non-model form is used for a set of individual field values and they are mostly used for things like search forms or other data that won’t be saved to the database.

Add the search to the interface

Now that we have the code for the search form, we should probably create a brand new page template for it.

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?

Change your app’s layout to include the search functionality. If you refresh each of the pages in your application, the search field should appear in the top-right corner.

But how do we get access to the contents of the search field? Here’s the HTML that was generated within each page to produce the search form:

<form action="/client_workouts/find" method="post">
  <input id="search_string" name="search_string" type="text" />
  <input name="commit" type="submit" value="Search" />
</form>

So are the form parameters structured differently?

The 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 params[:client_workout].

But what about the form_tag helper? 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:

Now that you know how to retrieve the search string from a form, it’s time to build the server-side code in the controller to do the search. To begin with, we will just display the value in the search field on the console. That way we can check that the form is working correctly.

The form is going to be submitted to a path called /client_workouts/find. The default routes in config/routes.rb will be able to map this route for us. Highlight which of the default routes will be used:

map.connect ':controller/:action/:id'
map.connect ':controller/:action/:id.:format'

Next, create a find method in the controller to display the contents of the search field. Hint: You can display a string in the console window using the command.

_____________________________________________________________________________________________________

Now that you know how to retrieve the search string from the form, let’s start to build the server-side code in the controller to do the search. To begin with, we will just display the value in the search field on the console. That way we can check that the form is working correctly.

The form is going to be submitted to a path called /client_workouts/find. The default routes in config/routes.rb will be able to map this route for us. Highlight which of the default routes will be used:

Next, create a find method in the controller to display the contents of the search field. Hint: You can display a string in the console window using the command

Let’s try the code out. In any of the pages in the application, enter some search text and click the “Search” button.

Don’t worry if you get an error when you press the search button. It’s simply because we haven’t created a search results page template yet. The interesting output will be in the console where you are running your web server. Somewhere amidst a set of errors about the missing results template, you should see this:

So we’ve created a search form on each page, and we have some code on the server that can read the string the user is searching form.

Now we need to actually do the searching.

Applications often need to do more than create, read, update, and delete records.
You will need to design your own page sequences sometimes—and the easiest way to start is by considering the user’s point of view, and how they’ll use your application.
If you need a form that doesn’t match a model object, use form_tag instead of form_for.
You need to use _tag fields with a form_tag form.
params[:field_name] will give you the value of the field called :field_name in a non-model form.
puts "A string" will output a string to the console.

Q:
Q: When would I use a form_tag instead of a form_for?
A:
A: Use form_tag if your form is going to be editing data that is not stored in a model object. We used a form_tag for the search form because there was no model object to with a single search_string attribute.
Q:
Q: Why are form_for and form_tag helpers surrounded by <% ... %> instead of <%= ... %>?
A:
A: The form helpers are used with scriptlets (<%...%>) instead of expressions (<%=...%>) because they do more than simply generate HTML. Remember that we used scriptlets for for loops? That’s because a for loop controls the contents of the code in its loop body. In a similar way, forms “control” the HTML generation of the field helpers they contain.
Q:
Q: That sounds sort of complicated...
A:
A: That’s OK—you don’t have to understand it all right now. If you just remember to use form_for and form_tag with scriptlets, everything will work fine.
Q:
Q: Can I still read the individual fields in a form_for model form?
A:
A: Yes. The form fields for the client_workout forms can be retrieved with params[:client_workout]—and this is just another hash. So to get the value of the ’trainer’ field you would just use params[:client_workout][:trainer].
Q:
Q: We used a default route for the “find” action. Couldn’t we have used those before?
A:
A: No. The routes that we used before didn’t match the default routes closely enough.
Q:
Q: How do I know when I need to create a custom route?
A:
A: If you type rake routes at the command line, you will see all of the routes available in your application. If none of them match, or if there’s a route that matches the wrong action, then you need to add a custom route.

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.

Brain Power

Think about the data in the underlying database table. What does it mean for a record in the table to match the search criteria? Is there something that would be true for matching records and false for the rest?

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

Lots of applications need to find all of the records with a certain value in a database column, so Rails makes that really easy to do.

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.

Complete the code for the find method.

def find
  @client_workouts = _______________________________________________________
end

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.

Create a page template for the find method to display the list of workouts displaying:

<Trainer name> <Workout duration> <Date of the workout> <Amount paid>

Hint: The index page scaffolding already generated is similar to what you need to produce.

_________________________________________________________________________________________

Create a page template for the find method to display the list of work outs displaying:

<Trainer name> <Workout duration> <Date of the workout> <Amount paid>

Hint: The index page is similar to what you need to produce.

Brain Power

How will this change the search criteria?

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?

Finders write database queries

So how exactly does a finder work? What happens when you run one? The finder’s job is to talk to the database on your behalf. Remember that when it’s called, a finder generates a query for the database in a language called SQL (structured query language).

Note

[Note from Marketing: Make sure you put in a plug for Head First SQL]

The query is used by the database to find all of the matching rows in the table. The model converts those rows into model objects and returns them in an array to the controller.

So given that’s how finders work, what needs to change if we are going to search for clients or trainers?

We need to be able to modify the conditions used in the SQL query

We need some way of telling the model to generate a SQL query that looks something like this:

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

The finders that are generated for each of the attributes are simple and easy to use, but the trouble is they’re not very flexible. You often need to make more complex queries on the database.

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:

The ?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?

The Fitness Club has started to record all of the games played on their outdoor baseball field. Match up the database finders with how they might be used.

Finder	Purpose
BaseballGame.find(:all, :conditions=>[ 'month_no > ? and month_no < ?', 9, 3])	Games played out of season
BaseballGame.find(:all, :conditions=>[ 'month_no > ? or month_no < ?', 3, 9])	Actually this query will never return anything, so it won’t be used
BaseballGame.find(:all, :conditions=>[ 'month_no > ? and month_no < ?', 3, 9])	Games played in season
BaseballGame.find(:all, :conditions=>[ 'month_no > ? or month_no < ?', 9, 3])	This query just returns everything, so it won’t be used either

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.

Rails Tools

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.

Ruby Tools

puts <string> displays a string on the console (the one running the web server)

Head First Rails by David Griffiths

Chapter 4. Database Finders: Truth or consequences?

Keep fit with the Rubyville Health Club

Note

Note

Note

The scaffolding isn’t right—but do we write our own code or fix the scaffolding?

The application actually looks pretty close...

We’re going to fix the scaffolding

Design the search function

So here’s what you need to build

Let’s start by building the form

The search will need a new kind of form

Add the search to the interface

Note

Note

Note

So are the form parameters structured differently?

How do we find client records?

Brain Power

We only need those records where client-name = the search string

There’s a finder for every attribute

So what’s next?

Brain Power

We need to match either the client name OR the trainer name

Can you see a problem here?

Note

We need to be able to modify the conditions used in the SQL query

Use :conditions to supply SQL

Then there’s a knock at the door...

Tools for your Rails Toolbox

Rails Tools

Ruby Tools

Don’t leave empty-handed

It’s yours, free.

Check it out now on O’Reilly