{"__v":1,"_id":"55097ae72dd6a11900e6e7b9","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:17:27.210Z","link_external":false,"link_url":"","githubsync":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"auth":"required","params":[],"url":""},"order":12,"body":"[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Working within CFML's Constraints to Deliver OOP-like Functionality\"\n}\n[/block]\nWheels makes up for the slowness of arrays of objects in CFML by providing *calculated properties*. With calculated properties, you can generate additional properties on the fly based on logic and data within your database.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Example #1: Full Name\"\n}\n[/block]\nConsider the example of `fullName`. If your database table has fields for `firstName` and `lastName`, it wouldn't make sense to store a third column called `fullName`. This would require more storage for redundant data, and it would add extra complexity that could lead to bugs and maintenance problems in the future.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Traditional Object-Oriented Calculations\"\n}\n[/block]\nIn most object-oriented languages, you would add a method to your class called `getFullName()`, which would return the concatenation of `this.firstName & \" \" & this.lastName`. The `getFullName()` method could potentially provide arguments to list the last name first and other types of calculations or transformations as well.\n\nWheels still allows for you to do this sort of dynamic calculation with the `returnAs=\"objects\"` argument in methods like [findAll()](doc:findall), but we advise against it when fetching large data sets because of the slowness of `CreateObject()` across CFML engines.\n\nSee the chapter on [Reading Records](doc:reading-records) for more information.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Using Calculated Properties to Generate fullName in the Database at Runtime\"\n}\n[/block]\nAs an alternative, you can set up a calculated property that dynamically performs the concatenation at the database level. In our example, we would write a line similar to this in our model's `init()` method:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset\\n    property(\\n        name=\\\"fullName\\\",\\n        sql=\\\"RTRIM(LTRIM(ISNULL(users.firstname, '') + ' '\\n            + ISNULL(users.lastname, '')))\\\"\\n    )\\n>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nAs you can probably deduce, we're creating a SQL statement that will be run in the `SELECT` clause to generate the `fullName.\n\nWith this line in place, `fullNam`e will become available in both full model objects and query objects returned by the various finder methods like [findAll()](doc:findall) and [findOne()](doc:findone).\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Example #2: Age\"\n}\n[/block]\nNaturally, if you store the user's birth date in the database, your application can use that data to dynamically calculate the user's age. Your app always knows how many years old the user is without needing to explicitly store his or her age.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Creating the Calculated Property for Age\"\n}\n[/block]\nIn order to calculate an extra property called `age` based on the `birthDate` column, our calculated property in `init()` may look something like this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset\\n    property(\\n        name=\\\"age\\\",\\n        sql=\\\"(CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT)\\n            - CAST(CONVERT(CHAR(8), users.date_of_birth, 112) AS INT))\\n            / 10000\\\"\\n    )\\n>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nMuch like the `fullName` example above, this will cause the database to add a property called `age` storing the user's age as an integer.\n\nNote that the cost to this approach is that you may need to introduce DBMS-specific code into your models. This may cause problems when you need to switch DBMS platforms, but at least all of this logic is isolated into your model CFCs.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Using the New age Property for Other Database Calculations\"\n}\n[/block]\nCalculated properties don't end at just generating extra properties. You can now also use the new property for additional calculations:\n\n* Creating additional properties with the `select` argument\n* Additional `where` clause calculations\n* Record sorting with `order`\n* Pagination\n* And so on…\n\nFor example, let's say that we only want to use `age` to return users who are in their 20s. We can use the new `age` property as if it existed in the database table. For extra measure, let's also sort the results from oldest to youngest.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset\\n    users = model(\\\"user\\\").findAll(\\n        where=\\\"age >= 20 AND age < 30\\\", order=\\\"age DESC\\\"\\n    )\\n>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]","excerpt":"Generate extra properties in your models on the fly without needing to store redundant data in your database.","slug":"calculated-properties","type":"basic","title":"Calculated Properties"}

Calculated Properties

Generate extra properties in your models on the fly without needing to store redundant data in your database.

[block:api-header] { "type": "basic", "title": "Working within CFML's Constraints to Deliver OOP-like Functionality" } [/block] Wheels makes up for the slowness of arrays of objects in CFML by providing *calculated properties*. With calculated properties, you can generate additional properties on the fly based on logic and data within your database. [block:api-header] { "type": "basic", "title": "Example #1: Full Name" } [/block] Consider the example of `fullName`. If your database table has fields for `firstName` and `lastName`, it wouldn't make sense to store a third column called `fullName`. This would require more storage for redundant data, and it would add extra complexity that could lead to bugs and maintenance problems in the future. [block:api-header] { "type": "basic", "title": "Traditional Object-Oriented Calculations" } [/block] In most object-oriented languages, you would add a method to your class called `getFullName()`, which would return the concatenation of `this.firstName & " " & this.lastName`. The `getFullName()` method could potentially provide arguments to list the last name first and other types of calculations or transformations as well. Wheels still allows for you to do this sort of dynamic calculation with the `returnAs="objects"` argument in methods like [findAll()](doc:findall), but we advise against it when fetching large data sets because of the slowness of `CreateObject()` across CFML engines. See the chapter on [Reading Records](doc:reading-records) for more information. [block:api-header] { "type": "basic", "title": "Using Calculated Properties to Generate fullName in the Database at Runtime" } [/block] As an alternative, you can set up a calculated property that dynamically performs the concatenation at the database level. In our example, we would write a line similar to this in our model's `init()` method: [block:code] { "codes": [ { "code": "<cfset\n property(\n name=\"fullName\",\n sql=\"RTRIM(LTRIM(ISNULL(users.firstname, '') + ' '\n + ISNULL(users.lastname, '')))\"\n )\n>", "language": "text" } ] } [/block] As you can probably deduce, we're creating a SQL statement that will be run in the `SELECT` clause to generate the `fullName. With this line in place, `fullNam`e will become available in both full model objects and query objects returned by the various finder methods like [findAll()](doc:findall) and [findOne()](doc:findone). [block:api-header] { "type": "basic", "title": "Example #2: Age" } [/block] Naturally, if you store the user's birth date in the database, your application can use that data to dynamically calculate the user's age. Your app always knows how many years old the user is without needing to explicitly store his or her age. [block:api-header] { "type": "basic", "title": "Creating the Calculated Property for Age" } [/block] In order to calculate an extra property called `age` based on the `birthDate` column, our calculated property in `init()` may look something like this: [block:code] { "codes": [ { "code": "<cfset\n property(\n name=\"age\",\n sql=\"(CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT)\n - CAST(CONVERT(CHAR(8), users.date_of_birth, 112) AS INT))\n / 10000\"\n )\n>", "language": "text" } ] } [/block] Much like the `fullName` example above, this will cause the database to add a property called `age` storing the user's age as an integer. Note that the cost to this approach is that you may need to introduce DBMS-specific code into your models. This may cause problems when you need to switch DBMS platforms, but at least all of this logic is isolated into your model CFCs. [block:api-header] { "type": "basic", "title": "Using the New age Property for Other Database Calculations" } [/block] Calculated properties don't end at just generating extra properties. You can now also use the new property for additional calculations: * Creating additional properties with the `select` argument * Additional `where` clause calculations * Record sorting with `order` * Pagination * And so on… For example, let's say that we only want to use `age` to return users who are in their 20s. We can use the new `age` property as if it existed in the database table. For extra measure, let's also sort the results from oldest to youngest. [block:code] { "codes": [ { "code": "<cfset\n users = model(\"user\").findAll(\n where=\"age >= 20 AND age < 30\", order=\"age DESC\"\n )\n>", "language": "text" } ] } [/block]