You are viewing documentation for v1.0.x. Change

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.

Class and Object Methods

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:

<cfset model("author")>

The built-in Wheels 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(), for example. 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.

<cfset authorClass = model("author")>
<cfset authorObject = authorClass.findByKey(1)>

For readability, this is usually combined into the following:

<cfset authorObject = model("author").findByKey(1)>

Now authorObject is an instance of the Author class, and you can call object level methods on it, like update() and save().

<cfset authorObject.update(firstName="Joe")>

In this case, the above code updates firstName field of the author record with a primary key value of 1 to Joe.

Primary Keys

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.

Tables and Classes

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.

<cfcomponent extends="Model">
</cfcomponent>

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.

<cfset auth = model("author").findByKey(1)>
<cfset auth.firstName = "Joe">
<cfset auth.save()>

This code makes use of the class method findByKey(), updates the object property in memory, and then saves it back to the database using the object method save(). We'll get back to all these methods and more later.

Table and CFC Naming

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() 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:

<cfcomponent extends="Model">
<cffunction name="init">
<cfset table("tbl_authors")>
</cffunction>
</cfcomponent>

Columns and Properties

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), Wheels 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() method like this:

<cfcomponent extends="Model">
<cffunction name="init">
<cfset property(name="firstName", column="tbl_auth_f_name")>
</cffunction>
</cfcomponent>
^ Top
Table of Contents

Comments

Read and submit questions, clarifications, and corrections about this chapter.

[Add Comment]

  1. Marcus Huffer's Gravatar Marcus Huffer says:

    Can I write my own sql queries using CFWheels.  Im asked the Dallas CFUG about your framework and no one there really uses it.  The only reply I did get that may point to a limitation of this framework is the fact that you can't write straight SQL queries when needed.  Im sure you can do this as well I just dont know how and neither did the guys that replied to my question.  Im researching frameworks since my company is looking into using one and I love what you have done so far!

  2. Chris Peters's Gravatar Chris Peters says:

    Marcus,

    We recommend creating an additional method in your model if you need to run a custom SQL query. So if you needed to call something like getDeletedUsers() (thus overriding Wheels's soft delete functionality), your model would look like this:

    <cfcomponent extends="Model">
        <cffunction name="getDeletedUsers">
            <cfquery datasource="dsn" name="qGetUsers">
                SELECT
                    *
                FROM
                    users
                WHERE
                    deletedat IS NOT NULL
            </cfquery>;
            <cfreturn qGetUsers>
        </cffunction>;
    </cfcomponent>;

    A simple example, but hopefully that'll lead you down the right path.

  3. Allen's Gravatar Allen says:

    Should the value for the datasource literally be "dsn"?

  4. Chris Peters's Gravatar Chris Peters says:

    @Allen: Not unless that's the name of your data source. If you set the data source name in config/database.cfm, then you could set the data source as #application.settings.database.datasource#.

    (Hint: In Wheels 0.9.1 and later, you will want to set it as #get('dataSourceName')#.)

  5. Dale's Gravatar Dale says:

    I prefer to use UUIDs as primar key values in my tables.  Do I need to write unique methods to handle this, or can I tell the ORM tht is what is being done somehow?

  6. Jim's Gravatar Jim says:

    I am a complete newbie on your framework. (in fact this is the first framework I have tried). I have been working with CF for about 6 months now so I guess I'm pretty new there too. As such, please excuse any silly questions, but I am wondering how you know when to put your methods into the models folder and when you just use the controllers. Love this framework so far btw.

  7. Seun Ojo's Gravatar Seun Ojo says:

    I am presently converting an application I build using my own framework to cfwheel. My first challenge is the lack of explicit support for Sybase, but i seem to have overcome that - i had to use a work around with the MS SQL Server Adapter since i could not get any help on the issue.

    However, i observe that the following global functions (model, get & set) are not available on my own OOP classes. i created a gateway folder directly on  the app_root and i have an empty application.cfc in it. The application just throws an error "Variable MODEL is undefined"; i have tried to comment/delet the application.cfc file, but i still get the same error.

    Since this functions are available on controllers, i figured i dumped the wheels.cfc that is on the controller package directly on my gateway folder, but that still didn't do the trick.

    I figure the issue has to do with the order of including files. But i dont know how exactly to go around it; but i do think global functions should indeed be global and there should be a way to tell the framework you want all the good juice in a certain folder without it fiddling with the request handling for the folder contents.

    Has anyone got an idea?

    Nice work btw... it's been inspiring...

  8. Johnny Thompson's Gravatar Johnny Thompson says:

    in my model (call it 'foobar.cfc') =

    <cffunction name="init">
    <cfset table("[someschema].tablename")>
    <cfset setPrimaryKey("someid")>
    </cffunction>;


    in my controller

                    <cfset myFooBar = model("foobar").findOne(where="name='fred'")>


    i get a crappy error message re: "Wheels.TableNotFound
    The [someschema].tablename table could not be found in the database.

    * the table DOES exists
    * if i run a query referencing it - it REQUIRES the schema name
    * if i don't include the schema name in the model init, i get a different error message from CF


    Error Executing Database Query.

    [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'tablename'.

    (but down in the SQL, ALL Of the columns are there, but obviously w/o the schema, it SQL Server won't allow the execution).


    what's up??

  9. dcolumbus's Gravatar dcolumbus says:

    This documentation is fantastic. May I suggest including a small example of how to deal with reference tables. 1 to 1 table relationships are simple with Wheels, but it would be very helpful to see an extended example.

  10. Kirby's Gravatar Kirby says:

    For those of you using Oracle, and having the same problem as Johnny Thompson, I have a fix.  In /wheels/model/adapters/Oracle.cfc, around line 165 (in the $getColumnInfo function), I added "AND TC.OWNER = 'schema'" to the where clause and my code is now working.

  11. Todd's Gravatar Todd says:

    I've been trying to learn wheels for the past few months but I have a rather newb question.  Would you mind posting some examples of custom class methods?

Add Comment