Reading Records
Returning records from your database tables as objects or queries.
Reading records from your database typically involves using one of the 3 finder methods available in Wheels: findByKey(), findOne(), and findAll().
The first 2 of these, findByKey() and findOne(), return an object, while the last one, findAll(), returns the result from a
cfquery tag.Let's start by looking at the simplest of the finder methods, findByKey(). This method takes one argument: the primary key (or several keys if you're using composite keys) of the record you want to get.
If the record exists, it is returned to you as an object. If not, Wheels will return the boolean value
false.In the following example, we assume that the
params.key variable has been created from the URL (for example a URL such as http://localhost/blog/viewauthor/7.)In your controller:
author = model("author").findByKey(params.key);
if(!IsObject(author)){
flashInsert(message="Author #params.key# was not found");
redirectTo(back=true);
}
In your view:
<cfoutput>Hello, #author.firstName# #author.lastName#!</cfoutput>
Often, you'll find yourself wanting to get a record (or many) based on a criteria other than just the primary key value.
As an example, let's say that you want to get the last order made by a customer. You can achieve this by using the
findOne() method like so:anOrder = model("order").findOne(order="datePurchased DESC");
You can use findAll() when you are asking to get one or more records from the database. Wheels will return this as a
cfquery result (which could be empty if nothing was found based on your criteria).This maps to the
SELECT clause of the SQL statement.Wheels is pretty smart when it comes to figuring out what to select from the database table(s). For example, if nothing is passed in to the
select argument, Wheels will assume that you want all columns returned and create a SELECTclause looking something like this:artists.id,artists.name
As you can see, Wheels knows that the
artist model is mapped to the artists table and will prepend the table name to the column names accordingly.If you have mapped columns to a different property name in your application, Wheels will take this into account as well. The end result then could look like this:
artists.id,artists.fname AS firstName
If you select from more than one table (see the
include argument below) and there are ambiguous column names, Wheels will sort this out for you by prepending the model name to the column name.Let's say you have a column called
name in both the artists and albums tables. The SELECT clause will be created like this:artists.name,albums.name AS albumName
If you use the include argument a lot, you will love this feature as it saves a lot of typing.
If you don't want to return all properties, you can override this behavior by passing in a list of the properties you want returned.
If you want to take full control over the
SELECT clause, you can do so by specifying the table names (i.e. author.firstName) in the select argument or by using alias names (i.e., firstname AS firstName). If Wheels comes across the use of any of these techniques, it will assume you know what you're doing and pass on the select argument straight to the SELECT clause with no changes.A tip is to turn on debugging when you're learning Wheels so you can get a good understanding of how Wheels creates the SQL statements.
This maps to the
WHERE clause of the SQL statement. Wheels will also convert all your input to cfqueryparam tags for you automatically.There are some limitations to what you can use in the
where argument, but the following SQL will work: =, !=, <>, <, <=, >, >=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, and OR. (Note that it's a requirement to write SQL keywords in upper case.) In addition to this, you can use parentheses to group conditional SQL statements together.It's worth mentioning that although Wheels does not support the
BETWEEN operator, you can get around this by using >= and <=.Example with numeric value:
items = model("item").findAll(where="price >= 100 AND price <= 500");
The same goes for
NOT BETWEEN:items = model("item").findAll(where="price <= 100 OR price >= 500");
In CFWheels ORM queries, you need to surround strings with single quotes or leave the quotes out if you're passing in a number or boolean.
Example with non-numeric value:
bobsArticles = model("author").findAll(where="firstName='Bob'");
This maps to the
ORDER clause of the SQL statement. If you don't specify an order at all, none will be used. (Makes sense, eh?) So in those cases, the database engine will decide in what order to return the records. Note that it's a requirement to write the SQL keywords ASC and DESC in upper case.There is one exception to this. If you paginate the records (by passing in the
page argument) without specifying the order, Wheels will order the results by the primary key column. This is because pagination relies on having unique records to order by.This is a powerful feature that you can use if you have set up associations in your models.
If, for example, you have specified that one
Author has many Articles, then you can return all authors and articles in the same call by doing this:bobsArticles = model("author").findAll(where="firstName='Bob'", include="Articles");
Set these if you want to get paginated data back.
So if you wanted records 11-20, for example, you write this code:
bobsArticles = model("author").findAll(
where="firstName='Bob'", include="Articles", page=2, perPage=10
);
This is the number of minutes to cache the query for. This is eventually passed on to the
cachedwithin attribute of the cfquery tag.In the beginning of this chapter, we said that you either get a query or an object back depending on the method that you call. But you can actually specify the return type so that you get either an object, a query, or an array of objects back.
To do this, you use the
returnAs argument. If you want an array of objects back from a findAll() call, for example, you can do this:users = model("user").findAll(returnAs="objects");
On findOne() and findByKey(), you can set this argument to either
object or query. On the findAll() method, you can set it to objects (note the plural) or query.We recommend sticking to this convention as much as possible because of the CFML engines' slow
CreateObject()function. Be careful when setting returnAs to objects. You won't want to create a lot of objects in your array and slow down your application unless you absolutely need to.If you have a specific index setup on a table that you'd like the
findAll() call to use, you can specify a structure of arguments for each model/index you'd like to use. Only MySQL and SQLServer support index hints.model("author").findAll(
include="posts",
useIndex={
author="idx_authors_123",
post="idx_posts_123"
}
);