LogoLogo
HomeAPIBlog
2.5.0
2.5.0
  • INTRODUCTION
    • Getting Started
      • Running Local Development servers
      • Beginner Tutorial: Hello World
      • Beginner Tutorial: Hello Database
      • Tutorial: CFWheels, AJAX, and You
    • Frameworks and CFWheels
    • Requirements
    • Manual Installation
    • Upgrading
    • Screencasts
  • Command Line Tools
    • CLI Commands
    • wheels - commands
    • wheels generate - commands
    • wheels dbmigrate - commands
    • wheels plugins - commands
  • Working with CFWheels
    • Conventions
    • Configuration and Defaults
    • Directory Structure
    • Switching Environments
    • Testing Your Application
    • Contributing to CFWheels
    • Documenting your Code
  • Handling Requests with Controllers
    • Request Handling
    • Rendering Content
    • Redirecting Users
    • Sending Files
    • Sending Email
    • Responding with Multiple Formats
    • Using the Flash
    • Using Filters
    • Verification
    • Event Handlers
    • Routing
    • URL Rewriting
      • Apache
      • IIS
      • Tomcat
      • Nginx
    • Obfuscating URLs
    • Caching
    • Nesting Controllers
    • CORS Requests
  • Displaying Views to Users
    • Pages
    • Partials
    • Linking Pages
    • Layouts
    • Form Helpers and Showing Errors
    • Displaying Links for Pagination
    • Date, Media, and Text Helpers
    • Creating Custom View Helpers
    • Localization
  • Database Interaction Through Models
    • Object Relational Mapping
    • Creating Records
    • Reading Records
    • Updating Records
    • Deleting Records
    • Column Statistics
    • Dynamic Finders
    • Getting Paginated Data
    • Associations
    • Nested Properties
    • Object Validation
    • Object Callbacks
    • Calculated Properties
    • Transactions
    • Dirty Records
    • Soft Delete
    • Automatic Time Stamps
    • Database Migrations
      • Migrations In Production
    • Using Multiple Data Sources
  • Plugins
    • Installing and Using Plugins
    • Developing Plugins
    • Publishing Plugins
  • External Links
    • Source Code
    • Issue Tracker
    • Sponsor Us
    • Community
Powered by GitBook
LogoLogo
On this page
  • Counting Rows
  • Getting an Average
  • Getting the Highest and Lowest Values
  • Getting the Sum of All Values
  • Grouping Your Results

Was this helpful?

Edit on GitHub
Export as PDF
  1. Database Interaction Through Models

Column Statistics

Use Wheels to get statistics on the values in a column, like row counts, averages, highest values, lowest values, and sums.

PreviousDeleting RecordsNextDynamic Finders

Last updated 1 year ago

Was this helpful?

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 cfquery and COUNT(id) AS x type queries when you wanted to get aggregate values, right?

Well, good news. Of course you don't need to do this; just use the built-in functions , , , and .

Let's start with the function, shall we?

Counting Rows

To count how many rows you have in your authors table, simply do this:

authorCount = model("author").count();

What if you only want to count authors with a last name starting with "A"? Like the function, will accept a where argument, so you can do this:

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 profiles and also setup a hasOne / belongsTo association between the author and profile models.

Just like in the function, you can now use the include argument to reference other tables.

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 countries table 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:

MySQL
SELECT COUNT(*)
FROM authors
WHERE ...

However, if you include a hasMany association, CFWheels will be smart enough to add the DISTINCT keyword 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 id is the primary key of the authors table and the correct associations have been setup):

MySQL
SELECT COUNT(DISTINCT authors.id)
FROM authors LEFT OUTER JOIN books ON authors.id = books.authorid
WHERE ..

Getting an Average

The same goes for the remaining column statistics functions as well; they all accept the property argument.

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=true to this function if you want to include only each unique instance of a value in the average calculation.

Getting the Highest and Lowest Values

They are pretty self explanatory, as you can tell by the following examples:

highestSalary = model("employee").maximum("salary");
lowestSalary = model("employee").minimum("salary");

Getting the Sum of All Values

Let's wrap up this chapter on a happy note by getting the total dollar amount you've made:

howRichAmI = model("invoice").sum("billedAmount");

Grouping Your Results

All of the methods we've covered in this chapter accepts the group argument. 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 cfquery result set back, as opposed to a single value.

Limited Support

The group argument is currently only supported on SQL Server and MySQL databases.

OK, so now we've covered the function, but there are a few other functions you can use as well to get column statistics.

You can use the function to get the average value on any given column. The difference between this function and the function is that this operates on a single column, while the function operates on complete records. Therefore, you need to pass in the name of the property you want to get an average for.

To get the highest and lowest values for a property, you can use the and functions.

The last of the column statistics functions is the function.

As you have probably already figured out, 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 distinct).

sum()
minimum()
maximum()
average()
count()
count()
findAll()
count()
findAll()
count()
average()
count()
count()
minimum()
maximum()
sum()
sum()