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
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
In the following example, we assume that the
params.keyvariable has been created from the URL (for example a URL such as
In your controller:
author = model("author").findByKey(params.key);
flashInsert(message="Author #params.key# was not found");
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
cfqueryresult (which could be empty if nothing was found based on your criteria).
This maps to the
SELECTclause 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
selectargument, Wheels will assume that you want all columns returned and create a
SELECTclause looking something like this:
As you can see, Wheels knows that the
artistmodel is mapped to the
artiststable 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
includeargument 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
namein both the
SELECTclause 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
SELECTclause, you can do so by specifying the table names (i.e.
author.firstName) in the
selectargument 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
SELECTclause 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
WHEREclause of the SQL statement. Wheels will also convert all your input to
cfqueryparamtags for you automatically.
There are some limitations to what you can use in the
whereargument, 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
BETWEENoperator, you can get around this by using
Example with numeric value:
items = model("item").findAll(where="price >= 100 AND price <= 500");
The same goes for
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
ORDERclause 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
DESCin upper case.
There is one exception to this. If you paginate the records (by passing in the
pageargument) 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
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
cachedwithinattribute of the
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
returnAsargument. 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
query. On the findAll() method, you can set it to
objects(note the plural) or
We recommend sticking to this convention as much as possible because of the CFML engines' slow
CreateObject()function. Be careful when setting
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.