{"__v":7,"_id":"55097ac74c7c3f2300aabf0b","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":["554ea706d119280d003e93c2"],"createdAt":"2015-03-18T13:16:55.347Z","link_external":false,"link_url":"","githubsync":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"auth":"required","params":[],"url":""},"order":8,"body":"*Associations* in Wheels allow you to define the relationships between your database tables. After configuring these relationships, doing pesky table joins becomes a trivial task. And like all other ORM functions in Wheels, this is done without writing a single line of SQL.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"3 Types of Associations\"\n}\n[/block]\nIn order to set up associations, you only need to remember 3 simple methods. Considering that the human brain only reliably remembers up to 7 items, we've left you with a lot of extra space. You're welcome. :)\n\nThe association methods should always be called in the `init()` method of a model that relates to another model within your application.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"The belongsTo Association\"\n}\n[/block]\nIf your database table contains a field that is a foreign key to another table, then this is where to use the [belongsTo()](doc:belongsto) function.\n\nIf we had a comments table that contains a foreign key to the posts table called `postid`, then we would have this `init()` method within our comment model:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfcomponent extends=\\\"Model\\\">\\n\\n    <cffunction name=\\\"init\\\">\\n        <cfset belongsTo(\\\"post\\\")>\\n    </cffunction>\\n\\n</cfcomponent>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"The hasOne and hasMany Associations\"\n}\n[/block]\nOn the other side of the relationship are the \"has\" functions. As you may have astutely guessed, these functions should be used according to the nature of the model relationship.\n\nAt this time, you need to be a little eccentric and talk to yourself. Your association should make sense in plain English language.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"An example of hasMany\"\n}\n[/block]\nSo let's consider the `post / comment` relationship mentioned above for `belongsTo()`. If we were to talk to ourselves, we would say, \"A post has many comments.\" And that's how you should construct your post model:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfcomponent extends=\\\"Model\\\">\\n\\n    <cffunction name=\\\"init\\\">\\n        <cfset hasMany(\\\"comments\\\")>\\n    </cffunction>\\n\\n</cfcomponent>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nYou may be a little concerned because our model is called `comment` and not `comments`. No need to worry: Wheels understands the need for the plural in conjunction with the `hasMany()` method.\n\nAnd don't worry about those pesky words in the English language that aren't pluralized by just adding an \"s\" to the end. Wheels is smart enough to know that words like \"deer\" and \"children\" are the plurals of \"deer\" and \"child,\" respectively.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"An Example of hasOne\"\n}\n[/block]\nThe [hasOne()](doc:hasone) association is not used as often as the [hasMany()](doc:hasmany) association, but it has its use cases. The most common use case is when you have a large table that you have broken down into two or more smaller tables (a.k.a. denormalization) for performance reasons or otherwise.\n\nLet's consider an association between `user` and `profile`. A lot of websites allow you to enter required info such as name and email but also allow you to add optional information such as age, salary, and so on. These can of course be stored in the same table. But given the fact that so much information is optional, it would make sense to have the required info in a `users` table and the optional info in a `profiles` table. This gives us a `hasOne()` relationship between these two models: \"A user *has one* profile.\"\n\nIn this case, our `profile` model would look like this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfcomponent extends=\\\"Model\\\">\\n\\n    <cffunction name=\\\"init\\\">\\n        <cfset belongsTo(\\\"user\\\")>\\n    </cffunction>\\n\\n</cfcomponent>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nAnd our user model would look like this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfcomponent extends=\\\"Model\\\">\\n\\n    <cffunction name=\\\"init\\\">\\n        <cfset hasOne(\\\"profile\\\")>\\n    </cffunction>\\n\\n</cfcomponent>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nAs you can see, you do not pluralize \"profile\" in this case because there is only one profile.\n\nBy the way, as you can see above, the association goes both ways, i.e. a `user hasOne() profile`, and a `profile belongsTo()` a `user`. Generally speaking, all associations should be set up this way. This will give you the fullest API to work with in terms of the methods and arguments that Wheels makes available for you.\n\nHowever, this is not a definite requirement. Wheels associations are completely independent of one another, so it's perfectly OK to setup a [hasMany()](doc:hasmany)  association without specifying the related [belongsTo()](doc:belongsto) association.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Dependencies\"\n}\n[/block]\nA dependency is when an associated model relies on the existence of its parent. In example above, a `profile` is dependent on a `user`. When you delete the `user`, you would usually want to delete the `profile` as well.\n\nWheels makes this easy for you. When setting up your association, simply add the argument `dependent` with one of the following values, and Wheels will automatically deal with the dependency.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<!--- Instantiates the `profile` model and calls its `delete()` method --->\\n<cfset hasOne(name=\\\"profile\\\", dependent=\\\"delete\\\")>\\n\\n<!--- Quickly deletes the `profile` without instantiating it --->\\n<cfset hasOne(name=\\\"profile\\\", dependent=\\\"deleteAll\\\")> \\n\\n<!--- Sets the `userId` of the profile to `NULL` --->\\n<cfset hasOne(name=\\\"profile\\\", dependent=\\\"nullify\\\")>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nYou can create dependencies on `hasOne()` and `hasMany()` associations, but not `belongsTo()`.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Self-Joins\"\n}\n[/block]\nIt's possible for a model to be associated to itself. Take a look at the below setup where an employee belongs to a manager for example:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"// In Employee.cfc\\nbelongsTo(name=\\\"manager\\\", modelName=\\\"employee\\\", foreignKey=\\\"managerId\\\");\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nBoth the manager and employee are stored in the same `employees` table and share the same `Employee` model.\n\nWhen you use this association in your code, the `employees` table will be joined to itself using the `managerid` column. CFWheels will handle the aliasing of the (otherwise duplicated) table names. It does this by using the pluralized version of the name you gave the association (in other words \"managers\" in this case).\n\nThis is important to remember because if you, for example, want to select the manager's name, you will have to do so manually (CFWheels won't do this for you, like it does with normal associations) using the `select` argument.\n\nHere's an example of how to select both the name of the employee and their manager:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"model(\\\"employee\\\").findAll(include=\\\"manager\\\", select=\\\"employees.name, managers.name AS managerName\\\")\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\n\n[block:callout]\n{\n  \"type\": \"info\",\n  \"title\": \"Know Your Joins\",\n  \"body\": \"Because the default `joinType` for `belongsTo()` is `inner`, employees without a manager assigned to them will not be returned in the `findAll()` call above. To return all rows you can set `jointype` to `outer` instead.\"\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Database Table Setup\"\n}\n[/block]\nLike everything else in Wheels, we strongly recommend a default naming convention for foreign key columns in your database tables.\n\nIn this case, the convention is to use the singular name of the related table with `id` appended to the end. So to link up our table to the `employees` table, the foreign key column should be named `employeeid`.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Breaking the Convention\"\n}\n[/block]\nWheels offers a way to configure your models to break this naming convention, however. This is done by using the `foreignKey` argument in your models' `belongsTo()` calls.\n\nLet's pretend that you have a relationship between`author` and `post`, but you didn't use the naming convention and instead called the column `post_id`. (You just can't seem to let go of the underscores, can you?)\n\nYour post's `init()` method would then need to look like this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfcomponent extends=\\\"Model\\\">\\n\\n    <cffunction name=\\\"init\\\">\\n        <cfset belongsTo(name=\\\"author\\\", foreignKey=\\\"post_id\\\")>\\n    </cffunction>\\n\\n</cfcomponent>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nYou can keep your underscores if it's your preference or if it's required of your application.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Leveraging Model Associations in Your Application\"\n}\n[/block]\nNow that we have our associations set up, let's use them to get some data into our applications.\n\nThere are a couple ways to join data via associations, which we'll go over now.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Using the include Argument in findAll()\"\n}\n[/block]\nTo join data from related tables in our [findAll()](doc:findall) calls, we simply need to use the include argument. Let's say that we wanted to include data about the author in our  [findAll()](doc:findall) call for `posts`.\n\nHere's what that call would look like:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset posts = model(\\\"post\\\").findAll(include=\\\"author\\\")>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nIt's that simple. Wheels will then join the `authors` table automatically so that you can use that data along with the data from `posts`.\n\nNote that if you switch the above statement around like this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset authors = model(\\\"author\\\").findAll(include=\\\"posts\\\")>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nThen you would need to specify \"post\" in its plural form, \"posts.\" If you're thinking about when to use the singular form and when to use the plural form, just use the one that seems most natural.\n\nIf you look at the two examples above, you'll see that in example #1, you're asking for all posts including each post's **author** (hence the singular \"author\"). In example #2, you're asking for all authors and all of the **posts** written by each author (hence the plural \"posts\").\n\nYou're not limited to specifying just one association in the `include` argument. You can for example return data for `authors, posts`, and `bios` in one call like this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset authorsPostsAndComments = model(\\\"author\\\").findAll(include=\\\"posts,bio\\\")>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nTo include several tables, simply delimit the names of the models with a comma. All models should contain related associations, or else you'll get a mountain of repeated data back.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Joining Tables Through a Chain of Associations\"\n}\n[/block]\nWhen you need to include tables more than one step away in a chain of joins, you will need to start using parenthesis. Look at the following example:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset commentsPostsAndAuthors = model(\\\"comment\\\").findAll(include=\\\"post(author)\\\")>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nThe use of parentheses above tells Wheels to look for an association named `author` on the `post` model instead of on the `comment` model. (Looking at the `comment` model is the default behavior when not using parenthesis.)\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Handling Column Naming Collisions\"\n}\n[/block]\nThere is a minor caveat to this approach. If you have a column in both associated tables with the same name, Wheels will pick just one to represent that column.\n\nIn order to include both columns, you can override this behavior with the `select` argument in the finder functions.\n\nFor example, if we had a column named `name` in both your `posts` and `authors` tables, then you could use the `select` argument like so:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset\\n    post = model(\\\"post\\\").findAll(\\n        select=\\\"posts.name, authors.id, authors.post_id, authors.name AS authorname\\\",\\n        include=\\\"author\\\"\\n    )\\n>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nYou would need to hard-code all column names that you need in that case, which does remove some of the simplicity. There are always trade-offs!\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Using Dynamic Shortcut Methods\"\n}\n[/block]\nA cool feature of Wheels is the ability to use *dynamic shortcut* methods to work with the models you have set up associations for. By dynamic, we mean that the name of the method depends on what name you have given the association when you set it up. By shortcut, we mean that the method usually delegates the actual processing to another Wheels method but gives you, the developer, an easier way to achieve the task (and makes your code more readable in the process).\n\nAs usual, this will make more sense when put into the context of an example. So let's do that right now.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Example: Dynamic Shortcut Methods for Posts and Comments\"\n}\n[/block]\nLet's say that you tell Wheels through a [hasMany()](doc:hasmany) call that a `post` *has many* `comments`. What happens then is that Wheels will enrich the post model by adding a bunch of useful methods related to this association.\n\nIf you wanted to get all `comments` that have been submitted for a `post`, you can now call `post.comments()`. In the background, Wheels will delegate this to a [findAll()](doc:findall) call with the `where` argument set to `postid=#post.id#`.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Listing of Dynamic Shortcut Methods\"\n}\n[/block]\nHere are all the methods that are added for the three possible association types.\n\n**Methods Added by hasMany** \n\nGiven that you have told Wheels that a `post` *has many* `comments` through a [hasMany()](doc:hasmany) call, here are the methods that will be made available to you on the `post` model.\n\nReplace `XXX` below with the name of the associated model (i.e. `comments` in the case of the example that we're using here).\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Method\",\n    \"h-1\": \"Example\",\n    \"h-2\": \"Description\",\n    \"0-0\": \"XXX()\",\n    \"1-0\": \"addXXX()\",\n    \"2-0\": \"removeXXX()\",\n    \"3-0\": \"deleteXXX()\",\n    \"4-0\": \"removeAllXXX()\",\n    \"5-0\": \"deleteAllXXX()\",\n    \"6-0\": \"XXXCount()\",\n    \"7-0\": \"newXXX()\",\n    \"8-0\": \"createXXX()\",\n    \"9-0\": \"hasXXX()\",\n    \"10-0\": \"findOneXXX()\",\n    \"0-1\": \"post.comments()\",\n    \"1-1\": \"post.addComment(comment)\",\n    \"2-1\": \"post.removeComment(comment)\",\n    \"3-1\": \"post.deleteComment(comment)\",\n    \"4-1\": \"post.removeAllComments()\",\n    \"5-1\": \"post.deleteAllComments()\",\n    \"6-1\": \"post.commentCount()\",\n    \"7-1\": \"post.newComment()\",\n    \"8-1\": \"post.createComment()\",\n    \"9-1\": \"post.hasComments()\",\n    \"10-1\": \"post.findOneComment()\",\n    \"1-2\": \"Adds a comment to the post association by setting its foreign key to the post's primary key value. Similar to calling `model(\\\"comment\\\").updateByKey(key=comment.id, postid=post.id)`.\",\n    \"2-2\": \"Removes a comment from the post association by setting its foreign key value to `NULL`. Similar to calling `model(\\\"comment\\\").updateByKey(key=comment.id, postid=\\\"\\\")`.\",\n    \"3-2\": \"Deletes the associated comment from the database table. Similar to calling `model(\\\"comment\\\").deleteByKey(key=comment.id)`.\",\n    \"4-2\": \"Removes all comments from the post association by setting their foreign key values to `NULL`. Similar to calling `model(\\\"comment\\\").updateAll(postid=\\\"\\\", where=\\\"postid=#post.id#\\\")`.\",\n    \"5-2\": \"Deletes the associated comments from the database table. Similar to calling `model(\\\"comment\\\").deleteAll(where=\\\"postid=#post.id#\\\")`.\",\n    \"6-2\": \"Returns the number of associated comments. Similar to calling `model(\\\"comment\\\").count(where=\\\"postid=#post.id#\\\")`.\",\n    \"7-2\": \"Creates a new comment object. Similar to calling `model(\\\"comment\\\").new(postid=post.id)`.\",\n    \"8-2\": \"Creates a new comment object and saves it to the database. Similar to calling `model(\\\"comment\\\").create(postid=post.id)`.\",\n    \"9-2\": \"Returns true if the post has any comments associated with it. Similar to calling `model(\\\"comment\\\").exists(where=\\\"postid=#post.id#\\\")`.\",\n    \"10-2\": \"Returns one of the associated comments. Similar to calling `model(\\\"comment\\\").findOne(where=\\\"postid=#post.id#\\\")`.\",\n    \"0-2\": \"Returns all comments where the foreign key matches the post's primary key value. Similar to calling `model(\\\"comment\\\").findAll(where=\\\"postid=#post.id#\\\")`.\"\n  },\n  \"cols\": 3,\n  \"rows\": 11\n}\n[/block]\n**Methods Added by hasOne**\n\nThe [hasOne()](doc:hasone) association adds a few methods as well. Most of them are very similar to the ones added by [hasMany()](doc:hasmany).\n\nGiven that you have told Wheels that an `author` *has one* `profile` through a [hasOne()](doc:hasone) call, here are the methods that will be made available to you on the `author` model.\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Method\",\n    \"h-1\": \"Example\",\n    \"h-2\": \"Description\",\n    \"0-0\": \"XXX()\",\n    \"1-0\": \"setXXX()\",\n    \"2-0\": \"removeXXX()\",\n    \"3-0\": \"deleteXXX()\",\n    \"4-0\": \"newXXX()\",\n    \"5-0\": \"createXXX()\",\n    \"6-0\": \"hasXXX()\",\n    \"0-1\": \"author.profile()\",\n    \"1-1\": \"author.setProfile(profile)\",\n    \"2-1\": \"author.removeProfile()\",\n    \"3-1\": \"author.deleteProfile()\",\n    \"4-1\": \"author.newProfile()\",\n    \"5-1\": \"author.createProfile()\",\n    \"6-1\": \"author.hasProfile()\",\n    \"0-2\": \"Returns the profile where the foreign key matches the author's primary key value. Similar to calling model(\\\"profile\\\").findOne(where=\\\"authorid=#author.id#\\\").\",\n    \"1-2\": \"Sets the profile to be associated with the author by setting its foreign key to the author's primary key value. You can pass in either a profile object or the primary key value of a profile object to this method. Similar to calling model(\\\"profile\\\").updateByKey(key=profile.id, authorid=author.id).\",\n    \"2-2\": \"Removes the profile from the author association by setting its foreign key to NULL. Similar to calling model(\\\"profile\\\").updateOne(where=\\\"authorid=#author.id#\\\", authorid=\\\"\\\").\",\n    \"3-2\": \"Deletes the associated profile from the database table. Similar to calling model(\\\"profile\\\").deleteOne(where=\\\"authorid=#author.id#\\\")\",\n    \"4-2\": \"Creates a new profile object. Similar to calling model(\\\"profile\\\").new(authorid=author.id).\",\n    \"5-2\": \"Creates a new profile object and saves it to the database. Similar to calling model(\\\"profile\\\").create(authorid=author.id).\",\n    \"6-2\": \"Returns true if the author has an associated profile. Similar to calling model(\\\"profile\\\").exists(where=\\\"authorid=#author.id#\\\").\"\n  },\n  \"cols\": 3,\n  \"rows\": 7\n}\n[/block]\n**Methods Added by belongsTo**\n\nThe [belongsTo()](doc:belongsto) association adds a couple of methods to your model as well.\n\nGiven that you have told Wheels that a `comment` belongs to a `post` through a [belongsTo()](doc:belongsto) call, here are the methods that will be made available to you on the `comment` model.\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Method\",\n    \"h-1\": \"Example\",\n    \"h-2\": \"Description\",\n    \"0-0\": \"XXX()\",\n    \"1-0\": \"hasXXX()\",\n    \"0-1\": \"comment.post()\",\n    \"1-1\": \"comment.hasPost()\",\n    \"0-2\": \"Returns the post where the primary key matches the comment's foreign key value. Similar to calling model(\\\"post\\\").findByKey(comment.postid).\",\n    \"1-2\": \"Returns true if the comment has a post associated with it. Similar to calling model(\\\"post\\\").exists(comment.postid).\"\n  },\n  \"cols\": 3,\n  \"rows\": 2\n}\n[/block]\nOne general rule for all of the methods above is that you can always supply any argument that is accepted by the method that the processing is delegated to. This means that you can, for example, call `post.comments(order=\"createdAt DESC\")`, and the `order` argument will be passed along to `findAll()`.\n\nAnother rule is that whenever a method accepts an object as its first argument, you also have the option of supplying the primary key value instead. This means that `author.setProfile(profile)` will perform the same task as `author.setProfile(1)`. (Of course, we're assuming that the `profile` object in this example has a primary key value of `1`.)\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Performance of Dynamic Association Finders\"\n}\n[/block]\nYou may be concerned that using a dynamic finder adds yet another database call to your application.\n\nIf it makes you feel any better, all calls in your Wheels request that generate the same SQL queries will be cached for that request. No need to worry about the performance implications of making multiple calls to the same `author.posts()` call in the scenario above, for example.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Passing Arguments to Dynamic Shortcut Methods\"\n}\n[/block]\nYou can also pass arguments to dynamic shortcut methods where applicable. For example, with the `XXX()` method, perhaps we'd want to limit a `post`'s comment listing to just ones created today. We can pass a `where` argument similar to what is passed to the `findAll()` function that powers `XXX()` behind the scenes.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset today = DateFormat(Now(), \\\"yyyy-mm-dd\\\")>\\n<cfset comments = post.comments(where=\\\"createdAt >= '#today# 00:00:00'\\\")>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Many-to-Many Relationships\"\n}\n[/block]\nWe can use the same 3 association functions to set up many-to-many table relationships in our models. It follows the same logic as the descriptions mentioned earlier in this chapter, so let's jump right into an example.\n\nLet's say that we wanted to set up a relationship between `customers` and `publications`. A `customer` can be subscribed to many `publications`, and `publications` can be subscribed to by many `customers`. In our database, this relationship is linked together by a third table called `subscriptions` (sometimes called a bridge entity by ERD snobs).\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Setting up the Models\"\n}\n[/block]\nHere are the representative models:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<!--- Customer.cfc --->\\n<cfcomponent extends=\\\"Model\\\">\\n\\n    <cffunction name=\\\"init\\\">\\n        <cfset hasMany(\\\"subscriptions\\\")>\\n    </cffunction>\\n\\n</cfcomponent>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\n\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<!--- Publication.cfc --->\\n<cfcomponent extends=\\\"Model\\\">\\n\\n    <cffunction name=\\\"init\\\">\\n        <cfset hasMany(\\\"subscriptions\\\")>\\n    </cffunction>\\n\\n</cfcomponent>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\n\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<!--- Subscription.cfc --->\\n<cfcomponent extends=\\\"Model\\\">\\n\\n    <cffunction name=\\\"init\\\">\\n        <cfset belongsTo(\\\"customer\\\")>\\n        <cfset belongsTo(\\\"publication\\\")>\\n    </cffunction>\\n\\n</cfcomponent>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nThis assumes that there are foreign key columns in `subscriptions` called `customerid` and `publicationid`.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Using Finders with a Many-to-Many Relationship\"\n}\n[/block]\nAt this point, it's still fairly easy to get data from the many-to-many association that we have set up above.\n\nWe can include the related tables from the `subscription` bridge entity to get the same effect:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset data = model(\\\"subscription\\\").findAll(include=\\\"customer,publication\\\")>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Creating a Shortcut for a Many-to-Many Relationship\"\n}\n[/block]\nWith the `shortcut` argument to [hasMany()](doc:hasmany), you can have Wheels create a dynamic method that lets you bypass the join model and instead reference the model on the other end of the many-to-many relationship directly.\n\nFor our example above, you can alter the [hasMany()](doc:hasmany) call on the `customer` model to look like this instead:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset hasMany(name=\\\"subscriptions\\\", shortcut=\\\"publications\\\")>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nNow you can get a customer's publications directly by using code like this:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset cust = model(\\\"customer\\\").findByKey(params.key)>\\n<cfset pubs = cust.publications()>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nThis functionality relies on having set up all the appropriate [hasMany()](doc:hasmany)  and [belongsTo()](doc:belongsto) associations in all 3 models (like we have in our example in this chapter).\n\nIt also relies on the association names being consistent, but if you have customized your association names, you can specify exactly which associations the shortcut method should use with the `through` argument.\n\nThe `through` argument accepts a list of 2 association names. The first argument is the name of the [belongsTo()](doc:belongsto) association (set in the `subscription` model in this case), and the second argument is the [hasMany()](doc:hasmany) association going back the other way (set in the `publication` model).\n\nSound complicated? That's another reason to stick to the conventions whenever possible: it keeps things simple.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Are You Still with Us?\"\n}\n[/block]\nAs you just read, Wheels offers a ton of functionality to make your life easier in working with relational databases. Be sure to give some of these techniques a try in your next Wheels application, and you'll be amazed at how little code that you'll need to write to interact with your database.","excerpt":"Through some simple configuration, Wheels allows you to unlock some powerful functionality to use your database tables' relationships in your code.","slug":"associations","type":"basic","title":"Associations"}

Associations

Through some simple configuration, Wheels allows you to unlock some powerful functionality to use your database tables' relationships in your code.

*Associations* in Wheels allow you to define the relationships between your database tables. After configuring these relationships, doing pesky table joins becomes a trivial task. And like all other ORM functions in Wheels, this is done without writing a single line of SQL. [block:api-header] { "type": "basic", "title": "3 Types of Associations" } [/block] In order to set up associations, you only need to remember 3 simple methods. Considering that the human brain only reliably remembers up to 7 items, we've left you with a lot of extra space. You're welcome. :) The association methods should always be called in the `init()` method of a model that relates to another model within your application. [block:api-header] { "type": "basic", "title": "The belongsTo Association" } [/block] If your database table contains a field that is a foreign key to another table, then this is where to use the [belongsTo()](doc:belongsto) function. If we had a comments table that contains a foreign key to the posts table called `postid`, then we would have this `init()` method within our comment model: [block:code] { "codes": [ { "code": "<cfcomponent extends=\"Model\">\n\n <cffunction name=\"init\">\n <cfset belongsTo(\"post\")>\n </cffunction>\n\n</cfcomponent>", "language": "text" } ] } [/block] [block:api-header] { "type": "basic", "title": "The hasOne and hasMany Associations" } [/block] On the other side of the relationship are the "has" functions. As you may have astutely guessed, these functions should be used according to the nature of the model relationship. At this time, you need to be a little eccentric and talk to yourself. Your association should make sense in plain English language. [block:api-header] { "type": "basic", "title": "An example of hasMany" } [/block] So let's consider the `post / comment` relationship mentioned above for `belongsTo()`. If we were to talk to ourselves, we would say, "A post has many comments." And that's how you should construct your post model: [block:code] { "codes": [ { "code": "<cfcomponent extends=\"Model\">\n\n <cffunction name=\"init\">\n <cfset hasMany(\"comments\")>\n </cffunction>\n\n</cfcomponent>", "language": "text" } ] } [/block] You may be a little concerned because our model is called `comment` and not `comments`. No need to worry: Wheels understands the need for the plural in conjunction with the `hasMany()` method. And don't worry about those pesky words in the English language that aren't pluralized by just adding an "s" to the end. Wheels is smart enough to know that words like "deer" and "children" are the plurals of "deer" and "child," respectively. [block:api-header] { "type": "basic", "title": "An Example of hasOne" } [/block] The [hasOne()](doc:hasone) association is not used as often as the [hasMany()](doc:hasmany) association, but it has its use cases. The most common use case is when you have a large table that you have broken down into two or more smaller tables (a.k.a. denormalization) for performance reasons or otherwise. Let's consider an association between `user` and `profile`. A lot of websites allow you to enter required info such as name and email but also allow you to add optional information such as age, salary, and so on. These can of course be stored in the same table. But given the fact that so much information is optional, it would make sense to have the required info in a `users` table and the optional info in a `profiles` table. This gives us a `hasOne()` relationship between these two models: "A user *has one* profile." In this case, our `profile` model would look like this: [block:code] { "codes": [ { "code": "<cfcomponent extends=\"Model\">\n\n <cffunction name=\"init\">\n <cfset belongsTo(\"user\")>\n </cffunction>\n\n</cfcomponent>", "language": "text" } ] } [/block] And our user model would look like this: [block:code] { "codes": [ { "code": "<cfcomponent extends=\"Model\">\n\n <cffunction name=\"init\">\n <cfset hasOne(\"profile\")>\n </cffunction>\n\n</cfcomponent>", "language": "text" } ] } [/block] As you can see, you do not pluralize "profile" in this case because there is only one profile. By the way, as you can see above, the association goes both ways, i.e. a `user hasOne() profile`, and a `profile belongsTo()` a `user`. Generally speaking, all associations should be set up this way. This will give you the fullest API to work with in terms of the methods and arguments that Wheels makes available for you. However, this is not a definite requirement. Wheels associations are completely independent of one another, so it's perfectly OK to setup a [hasMany()](doc:hasmany) association without specifying the related [belongsTo()](doc:belongsto) association. [block:api-header] { "type": "basic", "title": "Dependencies" } [/block] A dependency is when an associated model relies on the existence of its parent. In example above, a `profile` is dependent on a `user`. When you delete the `user`, you would usually want to delete the `profile` as well. Wheels makes this easy for you. When setting up your association, simply add the argument `dependent` with one of the following values, and Wheels will automatically deal with the dependency. [block:code] { "codes": [ { "code": "<!--- Instantiates the `profile` model and calls its `delete()` method --->\n<cfset hasOne(name=\"profile\", dependent=\"delete\")>\n\n<!--- Quickly deletes the `profile` without instantiating it --->\n<cfset hasOne(name=\"profile\", dependent=\"deleteAll\")> \n\n<!--- Sets the `userId` of the profile to `NULL` --->\n<cfset hasOne(name=\"profile\", dependent=\"nullify\")>", "language": "text" } ] } [/block] You can create dependencies on `hasOne()` and `hasMany()` associations, but not `belongsTo()`. [block:api-header] { "type": "basic", "title": "Self-Joins" } [/block] It's possible for a model to be associated to itself. Take a look at the below setup where an employee belongs to a manager for example: [block:code] { "codes": [ { "code": "// In Employee.cfc\nbelongsTo(name=\"manager\", modelName=\"employee\", foreignKey=\"managerId\");", "language": "text" } ] } [/block] Both the manager and employee are stored in the same `employees` table and share the same `Employee` model. When you use this association in your code, the `employees` table will be joined to itself using the `managerid` column. CFWheels will handle the aliasing of the (otherwise duplicated) table names. It does this by using the pluralized version of the name you gave the association (in other words "managers" in this case). This is important to remember because if you, for example, want to select the manager's name, you will have to do so manually (CFWheels won't do this for you, like it does with normal associations) using the `select` argument. Here's an example of how to select both the name of the employee and their manager: [block:code] { "codes": [ { "code": "model(\"employee\").findAll(include=\"manager\", select=\"employees.name, managers.name AS managerName\")", "language": "text" } ] } [/block] [block:callout] { "type": "info", "title": "Know Your Joins", "body": "Because the default `joinType` for `belongsTo()` is `inner`, employees without a manager assigned to them will not be returned in the `findAll()` call above. To return all rows you can set `jointype` to `outer` instead." } [/block] [block:api-header] { "type": "basic", "title": "Database Table Setup" } [/block] Like everything else in Wheels, we strongly recommend a default naming convention for foreign key columns in your database tables. In this case, the convention is to use the singular name of the related table with `id` appended to the end. So to link up our table to the `employees` table, the foreign key column should be named `employeeid`. [block:api-header] { "type": "basic", "title": "Breaking the Convention" } [/block] Wheels offers a way to configure your models to break this naming convention, however. This is done by using the `foreignKey` argument in your models' `belongsTo()` calls. Let's pretend that you have a relationship between`author` and `post`, but you didn't use the naming convention and instead called the column `post_id`. (You just can't seem to let go of the underscores, can you?) Your post's `init()` method would then need to look like this: [block:code] { "codes": [ { "code": "<cfcomponent extends=\"Model\">\n\n <cffunction name=\"init\">\n <cfset belongsTo(name=\"author\", foreignKey=\"post_id\")>\n </cffunction>\n\n</cfcomponent>", "language": "text" } ] } [/block] You can keep your underscores if it's your preference or if it's required of your application. [block:api-header] { "type": "basic", "title": "Leveraging Model Associations in Your Application" } [/block] Now that we have our associations set up, let's use them to get some data into our applications. There are a couple ways to join data via associations, which we'll go over now. [block:api-header] { "type": "basic", "title": "Using the include Argument in findAll()" } [/block] To join data from related tables in our [findAll()](doc:findall) calls, we simply need to use the include argument. Let's say that we wanted to include data about the author in our [findAll()](doc:findall) call for `posts`. Here's what that call would look like: [block:code] { "codes": [ { "code": "<cfset posts = model(\"post\").findAll(include=\"author\")>", "language": "text" } ] } [/block] It's that simple. Wheels will then join the `authors` table automatically so that you can use that data along with the data from `posts`. Note that if you switch the above statement around like this: [block:code] { "codes": [ { "code": "<cfset authors = model(\"author\").findAll(include=\"posts\")>", "language": "text" } ] } [/block] Then you would need to specify "post" in its plural form, "posts." If you're thinking about when to use the singular form and when to use the plural form, just use the one that seems most natural. If you look at the two examples above, you'll see that in example #1, you're asking for all posts including each post's **author** (hence the singular "author"). In example #2, you're asking for all authors and all of the **posts** written by each author (hence the plural "posts"). You're not limited to specifying just one association in the `include` argument. You can for example return data for `authors, posts`, and `bios` in one call like this: [block:code] { "codes": [ { "code": "<cfset authorsPostsAndComments = model(\"author\").findAll(include=\"posts,bio\")>", "language": "text" } ] } [/block] To include several tables, simply delimit the names of the models with a comma. All models should contain related associations, or else you'll get a mountain of repeated data back. [block:api-header] { "type": "basic", "title": "Joining Tables Through a Chain of Associations" } [/block] When you need to include tables more than one step away in a chain of joins, you will need to start using parenthesis. Look at the following example: [block:code] { "codes": [ { "code": "<cfset commentsPostsAndAuthors = model(\"comment\").findAll(include=\"post(author)\")>", "language": "text" } ] } [/block] The use of parentheses above tells Wheels to look for an association named `author` on the `post` model instead of on the `comment` model. (Looking at the `comment` model is the default behavior when not using parenthesis.) [block:api-header] { "type": "basic", "title": "Handling Column Naming Collisions" } [/block] There is a minor caveat to this approach. If you have a column in both associated tables with the same name, Wheels will pick just one to represent that column. In order to include both columns, you can override this behavior with the `select` argument in the finder functions. For example, if we had a column named `name` in both your `posts` and `authors` tables, then you could use the `select` argument like so: [block:code] { "codes": [ { "code": "<cfset\n post = model(\"post\").findAll(\n select=\"posts.name, authors.id, authors.post_id, authors.name AS authorname\",\n include=\"author\"\n )\n>", "language": "text" } ] } [/block] You would need to hard-code all column names that you need in that case, which does remove some of the simplicity. There are always trade-offs! [block:api-header] { "type": "basic", "title": "Using Dynamic Shortcut Methods" } [/block] A cool feature of Wheels is the ability to use *dynamic shortcut* methods to work with the models you have set up associations for. By dynamic, we mean that the name of the method depends on what name you have given the association when you set it up. By shortcut, we mean that the method usually delegates the actual processing to another Wheels method but gives you, the developer, an easier way to achieve the task (and makes your code more readable in the process). As usual, this will make more sense when put into the context of an example. So let's do that right now. [block:api-header] { "type": "basic", "title": "Example: Dynamic Shortcut Methods for Posts and Comments" } [/block] Let's say that you tell Wheels through a [hasMany()](doc:hasmany) call that a `post` *has many* `comments`. What happens then is that Wheels will enrich the post model by adding a bunch of useful methods related to this association. If you wanted to get all `comments` that have been submitted for a `post`, you can now call `post.comments()`. In the background, Wheels will delegate this to a [findAll()](doc:findall) call with the `where` argument set to `postid=#post.id#`. [block:api-header] { "type": "basic", "title": "Listing of Dynamic Shortcut Methods" } [/block] Here are all the methods that are added for the three possible association types. **Methods Added by hasMany** Given that you have told Wheels that a `post` *has many* `comments` through a [hasMany()](doc:hasmany) call, here are the methods that will be made available to you on the `post` model. Replace `XXX` below with the name of the associated model (i.e. `comments` in the case of the example that we're using here). [block:parameters] { "data": { "h-0": "Method", "h-1": "Example", "h-2": "Description", "0-0": "XXX()", "1-0": "addXXX()", "2-0": "removeXXX()", "3-0": "deleteXXX()", "4-0": "removeAllXXX()", "5-0": "deleteAllXXX()", "6-0": "XXXCount()", "7-0": "newXXX()", "8-0": "createXXX()", "9-0": "hasXXX()", "10-0": "findOneXXX()", "0-1": "post.comments()", "1-1": "post.addComment(comment)", "2-1": "post.removeComment(comment)", "3-1": "post.deleteComment(comment)", "4-1": "post.removeAllComments()", "5-1": "post.deleteAllComments()", "6-1": "post.commentCount()", "7-1": "post.newComment()", "8-1": "post.createComment()", "9-1": "post.hasComments()", "10-1": "post.findOneComment()", "1-2": "Adds a comment to the post association by setting its foreign key to the post's primary key value. Similar to calling `model(\"comment\").updateByKey(key=comment.id, postid=post.id)`.", "2-2": "Removes a comment from the post association by setting its foreign key value to `NULL`. Similar to calling `model(\"comment\").updateByKey(key=comment.id, postid=\"\")`.", "3-2": "Deletes the associated comment from the database table. Similar to calling `model(\"comment\").deleteByKey(key=comment.id)`.", "4-2": "Removes all comments from the post association by setting their foreign key values to `NULL`. Similar to calling `model(\"comment\").updateAll(postid=\"\", where=\"postid=#post.id#\")`.", "5-2": "Deletes the associated comments from the database table. Similar to calling `model(\"comment\").deleteAll(where=\"postid=#post.id#\")`.", "6-2": "Returns the number of associated comments. Similar to calling `model(\"comment\").count(where=\"postid=#post.id#\")`.", "7-2": "Creates a new comment object. Similar to calling `model(\"comment\").new(postid=post.id)`.", "8-2": "Creates a new comment object and saves it to the database. Similar to calling `model(\"comment\").create(postid=post.id)`.", "9-2": "Returns true if the post has any comments associated with it. Similar to calling `model(\"comment\").exists(where=\"postid=#post.id#\")`.", "10-2": "Returns one of the associated comments. Similar to calling `model(\"comment\").findOne(where=\"postid=#post.id#\")`.", "0-2": "Returns all comments where the foreign key matches the post's primary key value. Similar to calling `model(\"comment\").findAll(where=\"postid=#post.id#\")`." }, "cols": 3, "rows": 11 } [/block] **Methods Added by hasOne** The [hasOne()](doc:hasone) association adds a few methods as well. Most of them are very similar to the ones added by [hasMany()](doc:hasmany). Given that you have told Wheels that an `author` *has one* `profile` through a [hasOne()](doc:hasone) call, here are the methods that will be made available to you on the `author` model. [block:parameters] { "data": { "h-0": "Method", "h-1": "Example", "h-2": "Description", "0-0": "XXX()", "1-0": "setXXX()", "2-0": "removeXXX()", "3-0": "deleteXXX()", "4-0": "newXXX()", "5-0": "createXXX()", "6-0": "hasXXX()", "0-1": "author.profile()", "1-1": "author.setProfile(profile)", "2-1": "author.removeProfile()", "3-1": "author.deleteProfile()", "4-1": "author.newProfile()", "5-1": "author.createProfile()", "6-1": "author.hasProfile()", "0-2": "Returns the profile where the foreign key matches the author's primary key value. Similar to calling model(\"profile\").findOne(where=\"authorid=#author.id#\").", "1-2": "Sets the profile to be associated with the author by setting its foreign key to the author's primary key value. You can pass in either a profile object or the primary key value of a profile object to this method. Similar to calling model(\"profile\").updateByKey(key=profile.id, authorid=author.id).", "2-2": "Removes the profile from the author association by setting its foreign key to NULL. Similar to calling model(\"profile\").updateOne(where=\"authorid=#author.id#\", authorid=\"\").", "3-2": "Deletes the associated profile from the database table. Similar to calling model(\"profile\").deleteOne(where=\"authorid=#author.id#\")", "4-2": "Creates a new profile object. Similar to calling model(\"profile\").new(authorid=author.id).", "5-2": "Creates a new profile object and saves it to the database. Similar to calling model(\"profile\").create(authorid=author.id).", "6-2": "Returns true if the author has an associated profile. Similar to calling model(\"profile\").exists(where=\"authorid=#author.id#\")." }, "cols": 3, "rows": 7 } [/block] **Methods Added by belongsTo** The [belongsTo()](doc:belongsto) association adds a couple of methods to your model as well. Given that you have told Wheels that a `comment` belongs to a `post` through a [belongsTo()](doc:belongsto) call, here are the methods that will be made available to you on the `comment` model. [block:parameters] { "data": { "h-0": "Method", "h-1": "Example", "h-2": "Description", "0-0": "XXX()", "1-0": "hasXXX()", "0-1": "comment.post()", "1-1": "comment.hasPost()", "0-2": "Returns the post where the primary key matches the comment's foreign key value. Similar to calling model(\"post\").findByKey(comment.postid).", "1-2": "Returns true if the comment has a post associated with it. Similar to calling model(\"post\").exists(comment.postid)." }, "cols": 3, "rows": 2 } [/block] One general rule for all of the methods above is that you can always supply any argument that is accepted by the method that the processing is delegated to. This means that you can, for example, call `post.comments(order="createdAt DESC")`, and the `order` argument will be passed along to `findAll()`. Another rule is that whenever a method accepts an object as its first argument, you also have the option of supplying the primary key value instead. This means that `author.setProfile(profile)` will perform the same task as `author.setProfile(1)`. (Of course, we're assuming that the `profile` object in this example has a primary key value of `1`.) [block:api-header] { "type": "basic", "title": "Performance of Dynamic Association Finders" } [/block] You may be concerned that using a dynamic finder adds yet another database call to your application. If it makes you feel any better, all calls in your Wheels request that generate the same SQL queries will be cached for that request. No need to worry about the performance implications of making multiple calls to the same `author.posts()` call in the scenario above, for example. [block:api-header] { "type": "basic", "title": "Passing Arguments to Dynamic Shortcut Methods" } [/block] You can also pass arguments to dynamic shortcut methods where applicable. For example, with the `XXX()` method, perhaps we'd want to limit a `post`'s comment listing to just ones created today. We can pass a `where` argument similar to what is passed to the `findAll()` function that powers `XXX()` behind the scenes. [block:code] { "codes": [ { "code": "<cfset today = DateFormat(Now(), \"yyyy-mm-dd\")>\n<cfset comments = post.comments(where=\"createdAt >= '#today# 00:00:00'\")>", "language": "text" } ] } [/block] [block:api-header] { "type": "basic", "title": "Many-to-Many Relationships" } [/block] We can use the same 3 association functions to set up many-to-many table relationships in our models. It follows the same logic as the descriptions mentioned earlier in this chapter, so let's jump right into an example. Let's say that we wanted to set up a relationship between `customers` and `publications`. A `customer` can be subscribed to many `publications`, and `publications` can be subscribed to by many `customers`. In our database, this relationship is linked together by a third table called `subscriptions` (sometimes called a bridge entity by ERD snobs). [block:api-header] { "type": "basic", "title": "Setting up the Models" } [/block] Here are the representative models: [block:code] { "codes": [ { "code": "<!--- Customer.cfc --->\n<cfcomponent extends=\"Model\">\n\n <cffunction name=\"init\">\n <cfset hasMany(\"subscriptions\")>\n </cffunction>\n\n</cfcomponent>", "language": "text" } ] } [/block] [block:code] { "codes": [ { "code": "<!--- Publication.cfc --->\n<cfcomponent extends=\"Model\">\n\n <cffunction name=\"init\">\n <cfset hasMany(\"subscriptions\")>\n </cffunction>\n\n</cfcomponent>", "language": "text" } ] } [/block] [block:code] { "codes": [ { "code": "<!--- Subscription.cfc --->\n<cfcomponent extends=\"Model\">\n\n <cffunction name=\"init\">\n <cfset belongsTo(\"customer\")>\n <cfset belongsTo(\"publication\")>\n </cffunction>\n\n</cfcomponent>", "language": "text" } ] } [/block] This assumes that there are foreign key columns in `subscriptions` called `customerid` and `publicationid`. [block:api-header] { "type": "basic", "title": "Using Finders with a Many-to-Many Relationship" } [/block] At this point, it's still fairly easy to get data from the many-to-many association that we have set up above. We can include the related tables from the `subscription` bridge entity to get the same effect: [block:code] { "codes": [ { "code": "<cfset data = model(\"subscription\").findAll(include=\"customer,publication\")>", "language": "text" } ] } [/block] [block:api-header] { "type": "basic", "title": "Creating a Shortcut for a Many-to-Many Relationship" } [/block] With the `shortcut` argument to [hasMany()](doc:hasmany), you can have Wheels create a dynamic method that lets you bypass the join model and instead reference the model on the other end of the many-to-many relationship directly. For our example above, you can alter the [hasMany()](doc:hasmany) call on the `customer` model to look like this instead: [block:code] { "codes": [ { "code": "<cfset hasMany(name=\"subscriptions\", shortcut=\"publications\")>", "language": "text" } ] } [/block] Now you can get a customer's publications directly by using code like this: [block:code] { "codes": [ { "code": "<cfset cust = model(\"customer\").findByKey(params.key)>\n<cfset pubs = cust.publications()>", "language": "text" } ] } [/block] This functionality relies on having set up all the appropriate [hasMany()](doc:hasmany) and [belongsTo()](doc:belongsto) associations in all 3 models (like we have in our example in this chapter). It also relies on the association names being consistent, but if you have customized your association names, you can specify exactly which associations the shortcut method should use with the `through` argument. The `through` argument accepts a list of 2 association names. The first argument is the name of the [belongsTo()](doc:belongsto) association (set in the `subscription` model in this case), and the second argument is the [hasMany()](doc:hasmany) association going back the other way (set in the `publication` model). Sound complicated? That's another reason to stick to the conventions whenever possible: it keeps things simple. [block:api-header] { "type": "basic", "title": "Are You Still with Us?" } [/block] As you just read, Wheels offers a ton of functionality to make your life easier in working with relational databases. Be sure to give some of these techniques a try in your next Wheels application, and you'll be amazed at how little code that you'll need to write to interact with your database.