{"__v":4,"_id":"55097a9faa9bd525001a065c","category":{"__v":16,"_id":"550974cc368a56170041475c","project":"55070e814bb83b2500ec9404","version":"550974cb368a561700414757","pages":["55097a674c7c3f2300aabf07","55097a8a4c7c3f2300aabf09","55097a92ad1f0523008ecbd4","55097a9faa9bd525001a065c","55097aa92dd6a11900e6e7b4","55097ab2ad1f0523008ecbd6","55097ac74c7c3f2300aabf0b","55097ace2dd6a11900e6e7b7","55097ad5dd77250d007369fa","55097adead1f0523008ecbd9","55097ae72dd6a11900e6e7b9","55097aefdd77250d007369fc","55097af8dd77250d00736a05","55097aff4c7c3f2300aabf0d","55097b07aa9bd525001a0660","55097b11dd77250d00736a07"],"reference":false,"createdAt":"2015-03-18T11:08:27.090Z","from_sync":false,"order":3,"slug":"database-interaction-through-models","title":"Database Interaction Through Models"},"project":"55070e814bb83b2500ec9404","user":"55070d24d30b3f190011b941","version":{"__v":1,"_id":"550974cb368a561700414757","forked_from":"55070e814bb83b2500ec9407","project":"55070e814bb83b2500ec9404","createdAt":"2015-03-18T12:51:23.709Z","releaseDate":"2015-03-18T12:51:23.709Z","categories":["550974cc368a561700414758","550974cc368a561700414759","550974cc368a56170041475a","550974cc368a56170041475b","550974cc368a56170041475c","550974cc368a56170041475d","550974cc368a56170041475e"],"is_hidden":false,"is_beta":false,"is_stable":true,"codename":"","version_clean":"1.4.0","version":"1.4"},"updates":[],"createdAt":"2015-03-18T13:16:15.592Z","link_external":false,"link_url":"","githubsync":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"auth":"required","params":[],"url":""},"order":5,"body":"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?\n\nWell, good news. Of course you don't need to do this; just use the built-in functions [sum()](doc:sum), [minimum()](doc:minimum), [maximum()](doc:maximum), [average()](doc:average) and [count()](doc:count).\n\nLet's start with the [count()](doc:count) function, shall we?\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Counting Rows\"\n}\n[/block]\nTo count how many rows you have in your `authors` table, simply do this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"authorCount = model(\\\"author\\\").count();\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nWhat if you only want to count authors with a last name starting with \"A\"? Like the [findAll()](doc:findall) function, [count()](doc:count) will accept a `where` argument, so you can do this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"authorCount = model(\\\"author\\\").count(where=\\\"lastName LIKE 'A%'\\\");\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nSimple 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.\n\nJust like in the [findAll()](doc:findall) function, you can now use the `include` argument to reference other tables.\n\nIn our case, the code would end up looking something like this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"authorCount = model(\\\"author\\\").count(include=\\\"profile\\\", where=\\\"countryId=1 AND lastName LIKE 'A%'\\\");\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nOr, if you care more about readability than performance, why not just join in the `countries` table as well?\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"authorCount = model(\\\"author\\\").count(include=\\\"profile(country)\\\", where=\\\"name='USA' AND lastName LIKE 'A%'\\\");\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nIn the background, these functions all perform SQL that looks like this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"SELECT COUNT(*)\\nFROM authors\\nWHERE ...\",\n      \"language\": \"mysql\"\n    }\n  ]\n}\n[/block]\nHowever, 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.\n\nFor example, the following method call:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"authorCount = model(\\\"author\\\").count(include=\\\"books\\\", where=\\\"title LIKE 'Wheels%'\\\");\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nWill execute this SQL (presuming `id` is the primary key of the `authors` table and the correct associations have been setup):\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"SELECT COUNT(DISTINCT authors.id)\\nFROM authors LEFT OUTER JOIN books ON authors.id = books.authorid\\nWHERE ..\",\n      \"language\": \"mysql\"\n    }\n  ]\n}\n[/block]\nOK, so now we've covered the [count()](doc:count) function, but there are a few other functions you can use as well to get column statistics.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Getting an Average\"\n}\n[/block]\nYou can use the [average()](doc:average) function to get the average value on any given column. The difference between this function and the [count()](doc:count) function is that this operates on a single column, while the [count()](doc:count) function operates on complete records. Therefore, you need to pass in the name of the property you want to get an average for.\n\nThe same goes for the remaining column statistics functions as well; they all accept the `property` argument.\n\nHere's an example of getting the average salary in a specific department:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"avgSalary = model(\\\"employee\\\").average(property=\\\"salary\\\", where=\\\"departmentId=1\\\");\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nYou 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.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Getting the Highest and Lowest Values\"\n}\n[/block]\nTo get the highest and lowest values for a property, you can use the [minimum()](doc:minimum) and [maximum()](doc:maximum) functions.\n\nThey are pretty self explanatory, as you can tell by the following examples:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"highestSalary = model(\\\"employee\\\").maximum(\\\"salary\\\");\\nlowestSalary = model(\\\"employee\\\").minimum(\\\"salary\\\");\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Getting the Sum of All Values\"\n}\n[/block]\nThe last of the column statistics functions is the [sum()](doc:sum) function.\n\nAs you have probably already figured out, [sum()](doc: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 `distinct`).\n\nLet's wrap up this chapter on a happy note by getting the total dollar amount you've made:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"howRichAmI = model(\\\"invoice\\\").sum(\\\"billedAmount\\\");\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Grouping Your Results\"\n}\n[/block]\nAll 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.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"avgSalaries = model(\\\"employee\\\").average(property=\\\"salary\\\", group=\\\"departmentId\\\");\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nWhen you choose to group results like this you get a `cfquery` result set back, as opposed to a single value.\n[block:callout]\n{\n  \"type\": \"info\",\n  \"title\": \"Limited Support\",\n  \"body\": \"The `group` argument is currently only supported on SQL Server and MySQL databases.\"\n}\n[/block]","excerpt":"Use Wheels to get statistics on the values in a column, like row counts, averages, highest values, lowest values, and sums.","slug":"column-statistics","type":"basic","title":"Column Statistics"}

Column Statistics

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 `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 [sum()](doc:sum), [minimum()](doc:minimum), [maximum()](doc:maximum), [average()](doc:average) and [count()](doc:count). Let's start with the [count()](doc:count) function, shall we? [block:api-header] { "type": "basic", "title": "Counting Rows" } [/block] To count how many rows you have in your `authors` table, simply do this: [block:code] { "codes": [ { "code": "authorCount = model(\"author\").count();", "language": "text" } ] } [/block] What if you only want to count authors with a last name starting with "A"? Like the [findAll()](doc:findall) function, [count()](doc:count) will accept a `where` argument, so you can do this: [block:code] { "codes": [ { "code": "authorCount = model(\"author\").count(where=\"lastName LIKE 'A%'\");", "language": "text" } ] } [/block] 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 [findAll()](doc:findall) function, you can now use the `include` argument to reference other tables. In our case, the code would end up looking something like this: [block:code] { "codes": [ { "code": "authorCount = model(\"author\").count(include=\"profile\", where=\"countryId=1 AND lastName LIKE 'A%'\");", "language": "text" } ] } [/block] Or, if you care more about readability than performance, why not just join in the `countries` table as well? [block:code] { "codes": [ { "code": "authorCount = model(\"author\").count(include=\"profile(country)\", where=\"name='USA' AND lastName LIKE 'A%'\");", "language": "text" } ] } [/block] In the background, these functions all perform SQL that looks like this: [block:code] { "codes": [ { "code": "SELECT COUNT(*)\nFROM authors\nWHERE ...", "language": "mysql" } ] } [/block] 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: [block:code] { "codes": [ { "code": "authorCount = model(\"author\").count(include=\"books\", where=\"title LIKE 'Wheels%'\");", "language": "text" } ] } [/block] Will execute this SQL (presuming `id` is the primary key of the `authors` table and the correct associations have been setup): [block:code] { "codes": [ { "code": "SELECT COUNT(DISTINCT authors.id)\nFROM authors LEFT OUTER JOIN books ON authors.id = books.authorid\nWHERE ..", "language": "mysql" } ] } [/block] OK, so now we've covered the [count()](doc:count) function, but there are a few other functions you can use as well to get column statistics. [block:api-header] { "type": "basic", "title": "Getting an Average" } [/block] You can use the [average()](doc:average) function to get the average value on any given column. The difference between this function and the [count()](doc:count) function is that this operates on a single column, while the [count()](doc: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 `property` argument. Here's an example of getting the average salary in a specific department: [block:code] { "codes": [ { "code": "avgSalary = model(\"employee\").average(property=\"salary\", where=\"departmentId=1\");", "language": "text" } ] } [/block] 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. [block:api-header] { "type": "basic", "title": "Getting the Highest and Lowest Values" } [/block] To get the highest and lowest values for a property, you can use the [minimum()](doc:minimum) and [maximum()](doc:maximum) functions. They are pretty self explanatory, as you can tell by the following examples: [block:code] { "codes": [ { "code": "highestSalary = model(\"employee\").maximum(\"salary\");\nlowestSalary = model(\"employee\").minimum(\"salary\");", "language": "text" } ] } [/block] [block:api-header] { "type": "basic", "title": "Getting the Sum of All Values" } [/block] The last of the column statistics functions is the [sum()](doc:sum) function. As you have probably already figured out, [sum()](doc: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 `distinct`). Let's wrap up this chapter on a happy note by getting the total dollar amount you've made: [block:code] { "codes": [ { "code": "howRichAmI = model(\"invoice\").sum(\"billedAmount\");", "language": "text" } ] } [/block] [block:api-header] { "type": "basic", "title": "Grouping Your Results" } [/block] 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. [block:code] { "codes": [ { "code": "avgSalaries = model(\"employee\").average(property=\"salary\", group=\"departmentId\");", "language": "text" } ] } [/block] When you choose to group results like this you get a `cfquery` result set back, as opposed to a single value. [block:callout] { "type": "info", "title": "Limited Support", "body": "The `group` argument is currently only supported on SQL Server and MySQL databases." } [/block]