Chapter 4. Database Finders: Truth or consequences?

image with no caption

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.

image with no caption

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:

image with no caption

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:

image with no caption

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?

image with no caption

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.

image with no caption

So how can we find and display the workout sessions for an individual client?

image with no caption

Design the search function

Here’s what the search function should look like:

image with no caption

So here’s what you need to build

  1. A new search page where the trainer will enter the name of the client.

  2. A find action on the controller that will do the actual searching.

  3. 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:

image with no caption

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

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

image with no caption

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.

image with no caption

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?

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:

image with no caption

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:

image with no caption

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?

  1. 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.

  2. 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.

  3. 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.

image with no caption

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:

image with no caption

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:

image with no caption

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.

image with no caption

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.

image with no caption

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.

image with no caption

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:

image with no caption

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

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

image with no caption

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:

image with no caption

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:

image with no caption

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?

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.

image with no caption

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)

Get Head First Rails now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.