{"__v":4,"_id":"55097a674c7c3f2300aabf07","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":["55560d92ea5e120d00188457"],"createdAt":"2015-03-18T13:15:19.549Z","link_external":false,"link_url":"","githubsync":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"auth":"required","params":[],"url":""},"order":0,"body":"Mapping objects in your application to records in your database tables is a key concept in Wheels. Let's take a look at exactly how this mapping is performed.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Class and Object Methods\"\n}\n[/block]\nUnlike most other languages, there is no notion of class level (a.k.a. \"static\") methods in ColdFusion. This means that even if you call a method that does not need to use any instance data, you still have to create an object first.\n\nIn Wheels, we create an object like this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset model(\\\"author\\\")>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nThe built-in Wheels [model()](doc:model) function will return a reference to an `author` object in the `application` scope (unless it's the first time you call this function, in which case it will also create and store it in the `application` scope).\n\nOnce you have the `author` object, you can start calling class methods on it, like [findByKey()](doc:findbykey), for example. [findByKey()](doc:findbykey) returns an instance of the object with data from the database record defined by the key value that you pass.\n\nObviously, `author` is just an example here, and you'll use the names of the `.cfc` files you have created in the `models` folder.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset authorClass = model(\\\"author\\\")>\\n<cfset authorObject = authorClass.findByKey(1)>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nFor readability, this is usually combined into the following:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset authorObject = model(\\\"author\\\").findByKey(1)>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nNow `authorObject` is an instance of the `Author` class, and you can call object level methods on it, like [update()](doc:update) and [save()](doc:save).\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset authorObject.update(firstName=\\\"Joe\\\")>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nIn this case, the above code updates `firstName` field of the `author` record with a primary key value of `1` to `Joe`.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Primary Keys\"\n}\n[/block]\nTraditionally in Wheels, a primary key is usually named `id`, it increments automatically, and it's of the `integer` data type. However, Wheels is very flexible in this area. You can setup your primary keys in practically any way you want to. You can use *natural* keys (`varchar`, for example), *composite keys* (having multiple columns as primary keys), and you can name the key(s) whatever you want.\n\nYou can also choose whether the database creates the key for you (using auto-incrementation, for example) or create them yourself directly in your code.\n\nWhat's best, Wheels will introspect the database to see what choices you have made and act accordingly.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Tables and Classes\"\n}\n[/block]\nWheels comes with a custom built ORM. ORM stands for \"Object-Relational Mapping\" and means that tables in your relational database map to classes in your application. The records in your tables map to objects of your classes, and the columns in these tables map to properties on the objects.\n\nTo create a class in your application that maps to a table in your database, all you need to do is create a new class file in your `models` folder and make it extend the `Model.cfc` file.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfcomponent extends=\\\"Model\\\">\\n</cfcomponent>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nIf you don't intend to create any custom methods in your class, you can actually skip this step and just call methods without having a file created. It will work just as well. As your application grows, you'll probably want to have your own methods though, so remember the `models` folder. That's where they'll go.\n\nOnce you have created the file (or deliberately chosen not to for now), you will have a bunch of methods available handle reading and writing to the `authors` table. (For the purpose of showing some examples, we will assume that you have created a file named `Author.cfc`, which will then be mapped to the `authors` table in the database).\n\nFor example, you can write the following code to get the author with the primary key of `1`, change his first name, and save the record back to the database.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset auth = model(\\\"author\\\").findByKey(1)>\\n<cfset auth.firstName = \\\"Joe\\\">\\n<cfset auth.save()>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nThis code makes use of the class method [findByKey()](doc:findbykey), updates the object property in memory, and then saves it back to the database using the object method [save()](doc:save). We'll get back to all these methods and more later.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Table and CFC Naming\"\n}\n[/block]\nBy default, a table name should be the plural version of the class name. So if you have an `Author.cfc` class, the table name should be `authors`.\n\nTo change this behavior you can use the [table()](doc:table) method. This method call should be placed in the `init()` method of your class file.\n\nSo, for example, if you wanted for your `author` model to map to a table in your database named `tbl_authors`, you would add the following code to the `init()` method:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfcomponent extends=\\\"Model\\\">\\n    <cffunction name=\\\"init\\\">\\n        <cfset table(\\\"tbl_authors\\\")>\\n    </cffunction>\\n</cfcomponent>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Models Without Database Tables\"\n}\n[/block]\nMost of the time, you will want to have your model mapped to a database table. However, it is possible to skip this requirement with a simple setting:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cffunction name=\\\"init\\\">\\n    <cfset table(false)>\\n</cffunction>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nWith that in place, you have the foundation for a model that never touches the database. When you call methods like [save()](doc:save), [create()](doc:create), [update()](doc:update), and [delete()](doc:delete) on a tableless model, the entire model lifecycle will still run, including object validation and object callbacks.\n\nTypically, you will want to configure properties and validations manually for tableless models and then override [save()](doc:save) and other persistence methods needed by your application to persist with the data elsewhere (maybe in the `session` scope, an external NoSQL database, or as an email sent from a contact form).\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Columns and Properties\"\n}\n[/block]\nObjects in Wheels have properties that correspond to the columns in the table that it maps to. The first time you call a method on a model (or every time if you're in `design` mode), CFWheels will reflect on the schema inside the database for the table the class maps to and extract all the column information.\n\nTo keep things as simple as possible, there are no getters or setters in Wheels. Instead, all the properties are made available in the `this` scope.\n\nIf you want to map a specific property to a column with a different name, you can override the Wheels mapping by using the [property()](doc:property) method like this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfcomponent extends=\\\"Model\\\">\\n    <cffunction name=\\\"init\\\">\\n        <cfset property(name=\\\"firstName\\\", column=\\\"tbl_auth_f_name\\\")>\\n    </cffunction>\\n</cfcomponent>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Blank Strings and NULL Values\"\n}\n[/block]\nSince there is no concept of `null` / `nil` in ColdFusion (at least not in earlier versions), CFWheels will assume that when you save a blank string to the database it should be converted to `NULL`.\n\nFor this reason we recommend that you avoid having blank strings stored in the database (since there is no way to distinguish them from `NULL` values once they've been mapped to a CFWheels object / result set).","excerpt":"An overview of Object Relational Mapping (ORM) and how is it used in Wheels. Learn how ORM simplifies your database interaction code.","slug":"object-relational-mapping","type":"basic","title":"Object Relational Mapping"}

Object Relational Mapping

An overview of Object Relational Mapping (ORM) and how is it used in Wheels. Learn how ORM simplifies your database interaction code.

Mapping objects in your application to records in your database tables is a key concept in Wheels. Let's take a look at exactly how this mapping is performed. [block:api-header] { "type": "basic", "title": "Class and Object Methods" } [/block] Unlike most other languages, there is no notion of class level (a.k.a. "static") methods in ColdFusion. This means that even if you call a method that does not need to use any instance data, you still have to create an object first. In Wheels, we create an object like this: [block:code] { "codes": [ { "code": "<cfset model(\"author\")>", "language": "text" } ] } [/block] The built-in Wheels [model()](doc:model) function will return a reference to an `author` object in the `application` scope (unless it's the first time you call this function, in which case it will also create and store it in the `application` scope). Once you have the `author` object, you can start calling class methods on it, like [findByKey()](doc:findbykey), for example. [findByKey()](doc:findbykey) returns an instance of the object with data from the database record defined by the key value that you pass. Obviously, `author` is just an example here, and you'll use the names of the `.cfc` files you have created in the `models` folder. [block:code] { "codes": [ { "code": "<cfset authorClass = model(\"author\")>\n<cfset authorObject = authorClass.findByKey(1)>", "language": "text" } ] } [/block] For readability, this is usually combined into the following: [block:code] { "codes": [ { "code": "<cfset authorObject = model(\"author\").findByKey(1)>", "language": "text" } ] } [/block] Now `authorObject` is an instance of the `Author` class, and you can call object level methods on it, like [update()](doc:update) and [save()](doc:save). [block:code] { "codes": [ { "code": "<cfset authorObject.update(firstName=\"Joe\")>", "language": "text" } ] } [/block] In this case, the above code updates `firstName` field of the `author` record with a primary key value of `1` to `Joe`. [block:api-header] { "type": "basic", "title": "Primary Keys" } [/block] Traditionally in Wheels, a primary key is usually named `id`, it increments automatically, and it's of the `integer` data type. However, Wheels is very flexible in this area. You can setup your primary keys in practically any way you want to. You can use *natural* keys (`varchar`, for example), *composite keys* (having multiple columns as primary keys), and you can name the key(s) whatever you want. You can also choose whether the database creates the key for you (using auto-incrementation, for example) or create them yourself directly in your code. What's best, Wheels will introspect the database to see what choices you have made and act accordingly. [block:api-header] { "type": "basic", "title": "Tables and Classes" } [/block] Wheels comes with a custom built ORM. ORM stands for "Object-Relational Mapping" and means that tables in your relational database map to classes in your application. The records in your tables map to objects of your classes, and the columns in these tables map to properties on the objects. To create a class in your application that maps to a table in your database, all you need to do is create a new class file in your `models` folder and make it extend the `Model.cfc` file. [block:code] { "codes": [ { "code": "<cfcomponent extends=\"Model\">\n</cfcomponent>", "language": "text" } ] } [/block] If you don't intend to create any custom methods in your class, you can actually skip this step and just call methods without having a file created. It will work just as well. As your application grows, you'll probably want to have your own methods though, so remember the `models` folder. That's where they'll go. Once you have created the file (or deliberately chosen not to for now), you will have a bunch of methods available handle reading and writing to the `authors` table. (For the purpose of showing some examples, we will assume that you have created a file named `Author.cfc`, which will then be mapped to the `authors` table in the database). For example, you can write the following code to get the author with the primary key of `1`, change his first name, and save the record back to the database. [block:code] { "codes": [ { "code": "<cfset auth = model(\"author\").findByKey(1)>\n<cfset auth.firstName = \"Joe\">\n<cfset auth.save()>", "language": "text" } ] } [/block] This code makes use of the class method [findByKey()](doc:findbykey), updates the object property in memory, and then saves it back to the database using the object method [save()](doc:save). We'll get back to all these methods and more later. [block:api-header] { "type": "basic", "title": "Table and CFC Naming" } [/block] By default, a table name should be the plural version of the class name. So if you have an `Author.cfc` class, the table name should be `authors`. To change this behavior you can use the [table()](doc:table) method. This method call should be placed in the `init()` method of your class file. So, for example, if you wanted for your `author` model to map to a table in your database named `tbl_authors`, you would add the following code to the `init()` method: [block:code] { "codes": [ { "code": "<cfcomponent extends=\"Model\">\n <cffunction name=\"init\">\n <cfset table(\"tbl_authors\")>\n </cffunction>\n</cfcomponent>", "language": "text" } ] } [/block] [block:api-header] { "type": "basic", "title": "Models Without Database Tables" } [/block] Most of the time, you will want to have your model mapped to a database table. However, it is possible to skip this requirement with a simple setting: [block:code] { "codes": [ { "code": "<cffunction name=\"init\">\n <cfset table(false)>\n</cffunction>", "language": "text" } ] } [/block] With that in place, you have the foundation for a model that never touches the database. When you call methods like [save()](doc:save), [create()](doc:create), [update()](doc:update), and [delete()](doc:delete) on a tableless model, the entire model lifecycle will still run, including object validation and object callbacks. Typically, you will want to configure properties and validations manually for tableless models and then override [save()](doc:save) and other persistence methods needed by your application to persist with the data elsewhere (maybe in the `session` scope, an external NoSQL database, or as an email sent from a contact form). [block:api-header] { "type": "basic", "title": "Columns and Properties" } [/block] Objects in Wheels have properties that correspond to the columns in the table that it maps to. The first time you call a method on a model (or every time if you're in `design` mode), CFWheels will reflect on the schema inside the database for the table the class maps to and extract all the column information. To keep things as simple as possible, there are no getters or setters in Wheels. Instead, all the properties are made available in the `this` scope. If you want to map a specific property to a column with a different name, you can override the Wheels mapping by using the [property()](doc:property) method like this: [block:code] { "codes": [ { "code": "<cfcomponent extends=\"Model\">\n <cffunction name=\"init\">\n <cfset property(name=\"firstName\", column=\"tbl_auth_f_name\")>\n </cffunction>\n</cfcomponent>", "language": "text" } ] } [/block] [block:api-header] { "type": "basic", "title": "Blank Strings and NULL Values" } [/block] Since there is no concept of `null` / `nil` in ColdFusion (at least not in earlier versions), CFWheels will assume that when you save a blank string to the database it should be converted to `NULL`. For this reason we recommend that you avoid having blank strings stored in the database (since there is no way to distinguish them from `NULL` values once they've been mapped to a CFWheels object / result set).