Comment on page
Use Wheels to get statistics on the values in a column, like row counts, averages, highest values, lowest values, and sums.
Since CFWheels simplifies so much for you when you select, insert, update, and delete rows from the database, it would be a little annoying if you had to revert back to using
COUNT(id) AS xtype queries when you wanted to get aggregate values, right?
To count how many rows you have in your
authorstable, simply do this:
authorCount = model("author").count();
authorCount = model("author").count(where="lastName LIKE 'A%'");
Simple enough. But what if you wanted to count only authors in the USA, and that information is stored in a different table? Let's say you have stored country information in a table called
profilesand also setup a
belongsToassociation between the
In our case, the code would end up looking something like this:
authorCount = model("author").count(include="profile", where="countryId=1 AND lastName LIKE 'A%'");
Or, if you care more about readability than performance, why not just join in the
countriestable as well?
authorCount = model("author").count(include="profile(country)", where="name='USA' AND lastName LIKE 'A%'");
In the background, these functions all perform SQL that looks like this:
However, if you include a
hasManyassociation, CFWheels will be smart enough to add the
DISTINCTkeyword to the SQL. This makes sure that you're only counting unique rows.
For example, the following method call:
authorCount = model("author").count(include="books", where="title LIKE 'Wheels%'");
Will execute this SQL (presuming
idis the primary key of the
authorstable and the correct associations have been setup):
SELECT COUNT(DISTINCT authors.id)
FROM authors LEFT OUTER JOIN books ON authors.id = books.authorid
You can use the average() function to get the average value on any given column. The difference between this function and the count() function is that this operates on a single column, while the count() function operates on complete records. Therefore, you need to pass in the name of the property you want to get an average for.
The same goes for the remaining column statistics functions as well; they all accept the
Here's an example of getting the average salary in a specific department:
avgSalary = model("employee").average(property="salary", where="departmentId=1");
You can also pass in
distinct=trueto this function if you want to include only each unique instance of a value in the average calculation.
They are pretty self explanatory, as you can tell by the following examples:
highestSalary = model("employee").maximum("salary");
lowestSalary = model("employee").minimum("salary");
As you have probably already figured out, sum() adds all values for a given property and returns the result. You can use the same arguments as with the other functions (
property, where, include, and
Let's wrap up this chapter on a happy note by getting the total dollar amount you've made:
howRichAmI = model("invoice").sum("billedAmount");
All of the methods we've covered in this chapter accepts the
groupargument. Let's build on the example with getting the average salary for a department above, but this time, let's get the average for all departments instead.
avgSalaries = model("employee").average(property="salary", group="departmentId");
When you choose to group results like this you get a
cfqueryresult set back, as opposed to a single value.