One to One Relationship with a LinkTable

Application.cfc

<cfset this.name = "MyBlog_one2one_link">
<cfset this.ormenabled = "true">
<cfset this.ormsettings.datasource = "ORM_EG">
<cfset this.ormsettings.dbcreate = "dropcreate">

Employee.cfc

<cfcomponent persistent="true" table="employee">
    <cfproperty name="EmployeeID" generator="native">
    <cfproperty name="PersonalObj" fieldtype="one-to-one" linktable="EmployeePersonal" fkcolumn="EmployeeID" inversejoincolumn="PersonalID" cfc="personal" cascade="all" singularname="PersonalObj">
    <cfproperty name="LastName">
    <cfproperty name="FirstName">
</cfcomponent>

Personal.cfc

<cfcomponent persistent="true" table="personal">
    <cfproperty name="PersonalID" fieldtype="id" generator="native">
    <cfproperty name="EmployeeObj" fieldtype="one-to-one" cfc="Employee" linktable="EmployeePersonal" fkcolumn="PersonalID" inverseJoinColumn="EmployeeID" cascade="all" inverse="true">
    <cfproperty name="SSN">
    <cfproperty name="FatherName">
</cfcomponent>

index.cfm

        <cfset empObj = EntityNew("Employee")>
        <cfset empObj.setFirstName("James")>
        <cfset empObj.setLastName("Bond")>
        
        <cfset perObj = EntityNew("Personal")>
        <cfset perObj.setSSN("1-1-100")>
        <cfset perObj.setFatherName("Bond")>
        <cfset perObj.setEmployeeObj(empObj)>

        <cfset empObj.setPersonalObj(perObj)>
        <cfset EntitySave(empObj)>
        <cfset ormflush()>

One to Many Relationship with a LinkTable

Application.cfc

<cfset this.name = "MyBlog_one2many_link">
<cfset this.ormenabled = "true">
<cfset this.ormsettings.datasource = "ORM_EG">
<cfset this.ormsettings.dbcreate = "dropcreate">

Category.cfc

<cfcomponent persistent="true" table="category">
    <cfproperty name="CategoryID" fieldtype="id" generator="native">
    <cfproperty name="CategoryName">
    <cfproperty name="Description">
    <cfproperty name="products" fieldtype="one-to-many" linktable="CategoryProduct" type="array" cfc="product" inverse="true"  cascade="all-delete-orphan" fkcolumn="categoryid" inversejoincolumn="productid" lazy="false" singularname="products">
</cfcomponent>

Product.cfc

<cfcomponent persistent="true" table="product">
    <cfproperty name="ProductID" fieldtype="id" generator="native">
    <cfproperty name="ProductName">
    <cfproperty name="CategoryID" fieldtype="many-to-one" linktable="CategoryProduct" fkcolumn="ProductID" inversejoincolumn="CategoryID" cfc="category" singularname="Category">
</cfcomponent>

index.cfm

    <!---Inserting one category and two products--->
        <cfset catObj = EntityNew("category")>
        <cfset catObj.setCategoryName("Software")>
        <cfset catObj.setDescription("Computer Software")>

    <cfset prodObj1 = EntityNew("product")>
        <cfset prodObj1.setProductName("ColdFusion Server")>
        <cfset prodObj1.setCategoryID(catObj)>
        
    <cfset prodObj2 = EntityNew("product")>
        <cfset prodObj2.setProductName("ColdFusion Builder")>
        <cfset prodObj2.setCategoryID(catObj)>
      
        <cfset catObj.addProducts(prodobj1)>
        <cfset catObj.addProducts(prodObj2)>

        <cfset EntitySave(catObj)>
        <cfset ormflush()>

Many to Many Relationship

Application.cfc

<cfset this.name = "MyBlog_many2many">
<cfset this.ormenabled = "true">
<cfset this.ormsettings.datasource = "ORM_EG">
<cfset this.ormsettings.dbcreate = "dropcreate">

Employee.cfc

<cfcomponent persistent="true" table="employee">
    <cfproperty name="EmployeeID" generator="native">
    <cfproperty name="LastName">
    <cfproperty name="FirstName">
    <cfproperty name="TerritoryArr" fieldtype="many-to-many" linktable="EmployeeTerritory" type="array" cfc="Territory" cascade="all-delete-orphan" singularname="Territory">
</cfcomponent>

Territory.cfc

<cfcomponent persistent="true" table="territory">
    <cfproperty name="TerritoryID">
    <cfproperty name="TerritoryDescription">
    <cfproperty name="RegionID">
    <cfproperty name="EmployeeArr" fieldtype="many-to-many" linktable="Employeeterritory" type="array" cfc="Employee" cascade="all-delete-orphan" inverse="true" singularname="employee">
</cfcomponent>

index.cfm

        <cfset newEmpObj = EntityNew("Employee")>
        <cfset newEmpObj.setFirstName("James")>
        <cfset newEmpObj.setLastName("Bond")>
        
        <cfset newTerObj1 = EntityNew("Territory")>
        <cfset newTerObj1.setTerritoryID(1234)>
        <cfset newTerObj1.setTerritoryDescription("Earth")>
        <cfset newTerObj1.setRegionID(1)>
        
        <cfset newTerObj2 = EntityNew("Territory")>
        <cfset newTerObj2.setTerritoryID(4321)>
        <cfset newTerObj2.setTerritoryDescription("Moon")>
        <cfset newTerObj2.setRegionID(2)>
        
        <cfset newEmpObj.addTerritory(newTerObj1)>
        <cfset newEmpObj.addTerritory(newTerObj2)>
        
        <cfset EntitySave(newEmpObj)>
        <cfset ormflush()>
        
        <!---Remove one of the territories.--->
        <cfoutput>Remove one of the territories<br></cfoutput>
        <cfset newEmpObj.removeTerritory(newTerObj1)>
        <cfset ormflush()>

One to One Relationship continued...

This example shows how to define a one to one relationship between tables which have their own primary keys.
The relationship is established with the foreign key constraint on a column other than the primary key.

Application.cfc

<cfset this.name = "MyBlog_one2one_diff_pk">
<cfset this.datasource = "ORM_EG">
<cfset this.ormenabled = "true">

App.cfc

<cfcomponent persistent="true" table="Applications">
    <cfproperty name="ApplicationID" fieldtype="id" generator="native">
        <cfproperty name="TechObj" fieldtype="one-to-one" cfc="Tech" cascade="all" mappedby="ApplicationObj">
    <cfproperty name="ApplicationName">
</cfcomponent>

Tech.cfc

<cfcomponent persistent="true" table="Tech">
    <cfproperty name="TechID" generator="native" fieldtype="id">
        <cfproperty name="ApplicationObj" fieldtype="one-to-one" fkcolumn = "ApplicationId" cfc="App" >
    <cfproperty name="TechName">
</cfcomponent>

Inserting

<cfset appObj = EntityNew("App")>
<cfset appObj.setApplicationName("ColdFusion")>
        
<cfset techObj = EntityNew("Tech")>
<cfset techObj.setTechName("James")>
<cfset techObj.setApplicationObj(appObj)>

<cfset appObj.setTechObj(techObj)>
<cfset EntitySave(appObj)>
<cfset ormflush()>

One to One Relationship

This example shows how to define a one to one relationship between tables which share the same primary key

Application.cfc

<cfset this.name = "MyBlog_one2one_same_pk">
<cfset this.datasource = "ORM_EG">
<cfset this.ormenabled = "true">

Employee.cfc

<cfcomponent persistent="true" table="employees">
    <cfproperty name="EmployeeID" fieldtype="id" generator="native">
        <cfproperty name="PersonalObj" fieldtype="one-to-one" cfc="Personal" cascade="all">
    <cfproperty name="LastName">
    <cfproperty name="FirstName">
</cfcomponent>

Personal.cfc

<cfcomponent persistent="true" table="personal">
    <cfproperty name="PersonalID" generator="foreign" params="{property=EmployeeObj}">
        <cfproperty name="EmployeeObj" fieldtype="one-to-one" cfc="Employee" constrained="true">
    <cfproperty name="SSN">
    <cfproperty name="FatherName">
</cfcomponent>

Inserting

<cfset empObj = createobject("component", "Employee")>
<cfset empObj.setFirstName("James")>
<cfset empObj.setLastName("Bond")>
      
<cfset perObj = createobject("component", "Personal")>
<cfset perObj.setSSN("1-1-100")>
<cfset perObj.setFatherName("Bond")>
<cfset perObj.setEmployeeObj(empObj)>

<cfset empObj.setPersonalObj(perObj)>
<cfset EntitySave(empObj)>
<cfset ormflush()>

One to Many Relationship

Application.cfc

<cfset this.name = "MyBlog_one2many">
<cfset this.ormenabled = "true">
<cfset this.ormsettings.datasource = "ORM_EG">

Category.cfc

<cfcomponent persistent="true" table="categories">
    <cfproperty name="CategoryID" fieldtype="id" generator="native">
    <cfproperty name="CategoryName">
    <cfproperty name="Description">
    <cfproperty name="Picture">
    <cfproperty name="products" fieldtype="one-to-many" cfc="Product" cascade="all" fkcolumn="categoryid" lazy="false">
</cfcomponent>

Product.cfc

<cfcomponent persistent="true" table="products">
    <cfproperty name="ProductID" fieldtype="id" generator="native">
    <cfproperty name="ProductName">
    <cfproperty name="SupplierID">
    <cfproperty name="CategoryID" fieldtype="many-to-one" cfc="Category">
    <cfproperty name="Discontinued">
</cfcomponent>

Inserting one category and two products

<cfset catObj = createObject("component", "Category")>
<cfset catObj.setCategoryName("Indian")>
<cfset catObj.setDescription("Temporary Category")>

<cfset prodObj1 = createObject("component", "Product")>
<cfset prodObj1.setProductName("Rice")>
<cfset prodObj1.setDiscontinued(false)>
<cfset prodObj1.setCategoryID(catObj)>
     
<cfset prodObj2 = createObject("component", "Product")>
<cfset prodObj2.setProductName("Wheat")>
<cfset prodObj2.setDiscontinued(1)>
<cfset prodObj2.setCategoryID(catObj)>
      
<cfset catObj.addProducts(prodobj1)>
<cfset catObj.addProducts(prodObj2)>

<cfset EntitySave(catObj)>
<cfset ormflush()>

Updating the categories and products

<cfset catObj.setCategoryName("Indian1")>
<cfset prodArr = catObj.getProducts()>
<cfset prodArr[1].setProductName("Rice1")>
<cfset prodArr[2].setProductName("Wheat1")>
<cfset ormflush()>

Removing the products from the category

<cfset catObj = EntityLoad("Category", catModQuery.CategoryID, true)>
<cfoutput>catObj.removeProducts(prodobj1):#catObj.removeProducts(prodobj1)#<br></cfoutput>
<cfoutput>catObj.removeProducts(prodobj2):#catObj.removeProducts(prodobj2)#<br></cfoutput>
<cfset ormflush()>

Deleting all the entities

<cfset EntityDelete(catObj)>
<!---<cfset EntityDelete(prodobj1)>
<cfset EntityDelete(prodobj2)>--->
<cfset ormflush()>

Note that we have given cascade="all" in Category.cfc. Hence the associated product objects will also be deleted.

ORM CFC Mapping

Mapping a Column

<cfproperty name="FNAME" column="FirstName">

Mapping a Primary Key

<cfproperty name="ARTIST_ID" fieldtype="id" generator="native">

Mapping a Composite Key

<cfproperty name="ORDER_ID" fieldtype="id">
<cfproperty name="PRODUCT_ID" fieldtype="id">

Mapping a Computed Property

<cfcomponent persistent="true" table="ARTISTS">
    <cfproperty name="ID" column="ARTISTID" fieldtype="id"/>
    <cfproperty name="FIRSTNAME"/>
    <cfproperty name="LASTNAME"/>
    <cfproperty name="NumberOfArts" formula="select count(*) from Art art where art.ArtistID=ArtistID"/>
</cfcomponent>

You can also map a CFC without defining any properties.
ColdFusion will add all columns in the table specified as properties.

<cfcomponent persistent="true" table="ARTISTS">

</cfcomponent>

ORM - Basics

The minimum required ORM Settings.

Application.cfc

<cfset this.name = "ORM-Basic" >
<cfset this.ormenabled="true">
<cfset this.datasource="cfartgallery">

Defining an ORM - CFC.

Artists.cfc

<cfcomponent persistent="true">
<cfproperty column="ARTISTID" generator="increment" name="id">
<cfproperty name="FIRSTNAME">
<cfproperty name="LASTNAME">
<cfproperty name="ADDRESS">
<cfproperty name="CITY">
<cfproperty name="STATE">
<cfproperty name="POSTALCODE">
<cfproperty name="EMAIL">
<cfproperty name="PHONE">
<cfproperty name="FAX">
<cfproperty name="thepassword">
</cfcomponent>

Usage.

Initialize ORM

<cfset ormreload()>

Save

<cfset ArtistObj = entityNew("Artists")>
<cfset ArtistObj.setFIRSTNAME("James")>
<cfset ArtistObj.setLASTNAME("Bond")>
<cfset ArtistObj.setCITY("London")>
<cfset EntitySave(ArtistObj)>
<cfset ORMFlush()>

Load

<cfset ArtistArr = EntityLoad("Artists")>
<cfdump var="#ArtistArr#">

LoadByPK

<cfset ArtistObj = EntityLoadByPK("Artists",1)>
<cfdump var="#ArtistObj#">

Reload

<cfset ArtistObj.setCITY("Liverpool")>
<cfset entityReload(ArtistObj)>
<cfdump var="#ArtistObj.getCITY()#">

Delete

<cfset ArtistObj = EntityLoadByPK("Artists",1)>
<cfset EntityDelete(ArtistObj)>