ColdFusion 9.0 Resources

Define Relationships

Contents [Hide]

Relationship attributes
One-to-one relationships
One-to-many relationship
Many-to-one relationship
Many-to-many relationships

Relationship is the most crucial aspect of ORM. In a relational database, relation between tables are defined using foreign key. However, in case of objects, relation between two objects is defined using association where one object has a reference to another. ORM defines how the object relation is mapped to the database relation.

In this topic, relation and association would be used interchangeably.

Before you learn how to define the mapping for relation, it is important to understand few key concepts:

Source object: Object having the reference to the related object is termed as source of the relationship.
Target object: Object being referred or associated is termed as target of the relationship.
Direction and Navigability: In relational database, the relationship is always unidirectional, which implies that you can navigate from one table to another but not back to the same table. However, object model can be either unidirectional or bidirectional. A unidirectional association means that source has the reference to the target but the target does not know about the source. A bidirectional association means that both the objects have reference to each other and you can navigate from either object to another. In other words, source has a reference to the target and target also has a reference to the source. This also means that both the objects are source and target at the same time.

To set the association between objects, you need to set the references appropriately. For example, in case of Person-Address relation, where one person as one address, you need to associate Address to person as:
```
person.setAddress(address);
```
At this point, person object knows about the Address object but the address object does not know the person object. So, this is a unidirectional relation between Person-Address. To make this bidirectional, you need to associate Person to Address as:
```
address.setPerson(person);
```
Multiplicity: This defines how many target entities can a particular source have and how many source entities can a particular target have. Consider the example of artwork and artist, where an artist has many artwork pieces. In an object model, an artwork has reference to one artist and an artist has reference to many pieces of artwork. So, for artwork and artist the multiplicity is many-to-one and for artist and artwork, it is one-to-many. The other two type of multiplicities are one-to-one and many-to-many.

In this topic, multiplicity would be referred to as the type of relationship.

To indicate that a property defines the relationship between two persistent components, as a result of relationship in the database table, specify the fieldtype in the cfproperty tag to one of the following:

one-to-one
one-to-many
many-to-one
many-to-many

Relationship attributes

This table specifies the attribute details for all the relationship types.

The "Applies to" column indicates the relationship type that the attribute is applicable to; "all" indicates that the attribute is applicable to all relationship types.

Attribute	Applies to	Re/Opt	Default	Description
`batchsize`	one-to-many many-to-many	Optional		An integer value that specifies the "`batchsize`" for fetching uninitialized collections. For details, see Batch fetching.
`cacheuse`	one-to-many many-to-many	optional		Use this value to specify the caching strategy to be used for caching this component's data in the secondary cache. See Caching for details.
`cachename`	one-to-many many-to-many	optional	<entityname> <relationname>	Use this value to specify the name of the secondary cache. See Caching for details.
`cascade`	all	optional		See the Cascade options section for details.
`cfc`	all	Required		Name of the associated CFC.
`constrained`	one-to-one	Optional	false	Whether a constraint is set on this table's Primary Key column to reference the Primary Key in the other table: true false See One-to-one relationships for details.
`fetch`	all	Optional	select	Specifies whether join query or sequential select query will be used to retrieve the associated objects. The values are: join select See Lazy Loading for details.
`fieldtype`	all	Required	column	Specifies the type of relationship mapping: one-to-one one-to-many many-to-one many-to-many
`fkcolumn`	all	Optional		Specifies the foreign key column. In case the relation is established using link table, this specifies the foreign key column in the link table that references the primary key of the source object.
`foreignkeyname`	one-to-one many-to-one, many-to-many	optional	autogenerated	Specifies the name of the foreign key constraint. This is used only when the tables are created by ORM.
`index`	many-to-one	optional	false	Specifies the name of the index for the foreign key column.
`insert`	many-to-one	Optional	true	Specifies whether the column should be included in SQL UPDATE and/or INSERT statements. Values are: `true false` Set `update=false` and `insert=false` if the column needs to be read-only.
`inverse`	one-to-many many-to-many	Optional	false	Specifies whether SQL query should be executed for this association when persisting this object. Value are: true false See “Inverse” section for details.
`inversejoincolumn`	all	Optional		Specifies the foreign key column in the Join table that references the primary key column in the target table.
`lazy`	all	Optional	true	Specifies if the association should be loaded lazily. true false extra See Lazy Loading for details.
`linkcatalog`	all	Optional		Catalog for the link table.
`linkschema`	all	Optional		Schema for the link table.
`linktable`	all	Required		Name of the link table.
`mappedby`	all	Optional		In a relationship, the foreign key can reference a unique column other than the primary key. In such cases, use `mappedby` to specify the property name that maps to the unique key column.
`missingrowIgnored`	many-to-one many-to-many	Optional	false	Values are: true false If the value is true, and the row that is referenced by the foreign key is missing, it is treated as a null association. The default is false, in which case an exception is thrown.
`name`	all	Required		Name of the field
`notnull`	many-to-one	optional	false	Use this to add the not-null constraint for the foreign key column when the table is created by ORM.
`optimisticlock`	all	Optional	true	Whether updates to this property need acquisition of the optimistic lock on the table row. Values are: true false See Optimistic locking for details.
`orderby`	one-to-many many-to-many	Optional		Specifies the `orderby` string that needs to be used to sort the associated collection. Use the following format to specify this string: "col1 <asc/desc> (, col2<asc/desc>)" or "col1(, col2)" In the latter case, `asc` is taken as default.
`readonly`	one-to-many many-to-many	Optional	false	Values are: true false If set to true, it indicates that the collection never changes and can be cached.
remotingFetch	all	Optional	false	The value of the remotingFetch attribute is false by default for any property that shares one-to-one, one-to-many, many-to-one, or many-to-many relationship. Set this value to true to retrieve data on the client-side.
`singularname`	one-to-many many-to-many	optional	property name	Use this property to define the custom name for generated relationship methods. See Generated methods for relationships between CFCs.
`structkeycolumn`	one-to-many many-to-many type=struct			The column in the target table to use as key if the collection type is struct.
`structkeytype`	one-to-many many-to-many type =struct	Optional		Specifies the data type of the key, when type=struct. For the entire list of data types, see the Data Types section.
`type`	one-to-many many-to-many	Optional		Specifies the datatype of the relationship property: array struct
`update`	many-to-one	Optional	true	Specifies whether the column should be included in SQL update statement: true false Set update=false and insert=false if the column needs to be read-only.
`unique`	many-to-one	optional	false	Use this to add the unique constraint for the foreign key column when the table is created by ORM. This effectively makes this relation one-to-one.
`uniquekey`	many-to-one	optional		Groups columns in a single unique key constraint.
`where`	one-to-many many-to-many	optional		Use this to specify a SQL that will be used to filter the collection retrieved. See “Applying filters on relationship“ for details.

Cascade options

In an association, it is cumbersome to apply an action performed on object to the other object. For example, in case of an Department-Employee one-to-many association, if you add an employee, the same change needs to be updated on the Department as well. The cascade option in Hibernate allows you to perform such operations.

You can specify the following values in the cascade attribute:

all: Allows you to apply all operations to be cascaded to the associated object.
save-update: If the parent object is saved, the associated objects are saved as well.
delete: Deletes the child object if the delete operation is called on the parent object.
delete-orphan: This is a special kind of cascade option that applies to one-to-many relation only. Deletes all child objects for which the association has been removed.
all-delete-orphan: Cascades all operations to child objects, and performs delete-orphan action.
refresh: Cascades the refresh action to the child object. The refresh action is used to reload an object and its collections.

Typically, cascade attribute is not used on a many-to-one or a many-to-many relationship.

You can also specify comma separated cascade values in the cascade attribute. For a one-to-one or a one-to-many relationship, the most common values are all-delete-orphan.

For an association where the child object can continue to exist even if the parent object is deleted, you can apply the save-update cascade value.

Applying filter on associated objects

In one-to-many and many-to-many relationships, an array or struct is retrieved. Filters can be applied to retrieve a subset of the associated objects. The filter can be specified in the where attribute, which is an SQL where clause. In a one-to-many association for artist and artwork:

If you want to retrieve only the unsold artwork for every Artist object, you need to define the mapping as follows:

<cfproperty name="unsoldArts" cfc="Art" fieldtype="one-to-many" fkcolumn="ARTISTID" where="issold=0">

Inverse

In a bidirectional relationship, the inverse attribute is used on an association property to specify whether an SQL query should be executed for the association, when persisting the object.

Consider the ART and ARTIST CFCs, which have a bidirectional one-to-many relationship. This means that each ART object has a reference to the ARTIST object and ARTIST object has a reference to the ART object. While persisting ARTIST and the associated ART, the relationship can be established in the database from both sides of the object. Setting inverse=true on one side of the relation tells ORM to ignore this side of relation for executing the SQL.

As a general rule, in a bidirectional relation, one side must set inverse to true. For one-to-many or many-to-one relation, inverse should be set on the many side of the relation. For example, in ARTIST-ART relation, inverse should be set to true on the 'art' property in ARTIST.

In many-to-many relation, you can set inverse=true on any side of the relation.

One-to-one relationships

A one-to-one relationship is where the source object has an attribute that references another single target object and vice-versa. An example of this relationship is the relationship between an employee and the assigned office cubicle, where one employee has one office cubicle and one office cubicle belongs to only one employee.

A one-to-one relationship between two persistent components are defined using fieldtype value one-to-one.

Syntax:

<cfproperty name="fieldname" fieldtype="one-to-one" cfc="Referenced_CFC_Name"  cascade="cascade_options" constrained="true|[false]" fetch="join|[select]"  lazy="[true]|false">

There are two types of one-to-one relationships:

Primary key association
Unique Foreign Key association

Primary key association

In this type of association, the primary key of one table references to the primary key of another table. That is, both the tables share the same primary key.

The following example shows how to define this mapping.

Example

Consider the EMPLOYEE and OFFICECUBICLE example. Both the tables share the same primary key. The mapping for these tables are as follows:

EMPLOYEE.cfc

<cfcomponent persistent="true" table="Employee"> 
    <cfproperty name="id" fieldtype="id" generator="native"> 
    <cfproperty name="firstname"> 
    <cfproperty name="lastname"> 
    <cfproperty name="officecubicle" fieldtype="one-to-one" cfc="OfficeCubicle"> 
</cfcomponent>

OFFICECUBICLE.cfc
```
<cfcomponent persistent="true" table="OfficeCubicle"> 
    <cfproperty name="id" fieldtype="id" generator="foreign" params="{property='Employee'}"> 
    <cfproperty name="Employee" fieldtype="one-to-one" cfc="Employee" constrained="true"> 
    <cfproperty name="Location"> 
    <cfproperty name="Size"> 
</cfcomponent>
```
fieldtype=one-to-one specifies that the property is a one-to-one property.

constrained=true on Employee property in OFFICECUBICLE.cfc, means that a constraint is set on the OFFICECUBICLE table for its ID to reference the ID in the EMPLOYEE table.

The ID of EMPLOYEE table is auto-generated. The ID of the OFFICECUBICLE table should be the same as the ID of the Employee table. For this, set generator="foreign". Foreign generator takes one parameter 'property' as input, which should be the relationship property name of OFFICECUBICLE entity which is 'EMPLOYEE' in this case.

Here, primary key values of related rows in both the tables must be the same. The identity generator algorithm in the mapping for the component (whose mapped table has the constraint), must be set to foreign.

Unique foreign key association

In this type of association, the foreign key of one table references the primary key of another table, and the foreign key column has a unique constraint. To define this relationship, fkcolumn attribute should be specified on the relationship-property of the CFC whose table contains the foreign key column. The other end of relation should use mappedby attribute.

Syntax

<cfproperty name="fieldname" fieldtype="one-to-one" cfc="Referenced_CFC_Name" 
fkcolumn="foreign_key_column" 
mappedby="Mapped_Field_name_in_referenced_CFC" 
cascade="none" 
fetch="join|[select]" 
lazy="[true]|false">

Note: The mappedby attribute can not be specified with the fkcolumn attribute.

Example

In the EMPLOYEE and OFFICECUBICLE example, OFFICECUBICLE has a foreign key column, EMPLOYEEID. This foreign key references the primary key of the Employee table. OFFICECUBICLE has an auto-generated primary key, which does not participate in the relationship.

EMPLOYEE.cfc

<cfcomponent persistent="true" table="Employee"> 
    <cfproperty name="EmployeeID" fieldtype="id" generator="native"> 
    <cfproperty name="firstname"> 
    <cfproperty name="lastname"> 
    <cfproperty name="officecubicle" fieldtype="one-to-one" cfc="officecubicle" mappedby="Employee"> 
</cfcomponent>

OFFICECUBICLE.cfc

<cfcomponent persistent="true" table="officecubicle"> 
    <cfproperty name="id" fieldtype="id" generator="native"> 
    <cfproperty name="Employee" fieldtype="one-to-one" cfc="Employee" fkcolumn="EmployeeID"> 
    <cfproperty name="Location"> 
    <cfproperty name="Size"> 
</cfcomponent>

In OFFICECUBICLE entity, fkcolumn="EmployeeID" specifies that EmployeeID is the foreign key column in OFFICECUBICLE table.
mappedby="Employee" specifies that the one-to-one relationship is with the foreign-key property 'EMPLOYEE' in OFFICECUBICLE entity and not with its primary key.
In Employee entity, fkcolumn should not be specified.

In this case, OFFICECUBICLE entity has a independent Primary key which is auto-generated.

One-to-many relationship

A one-to-many relationship is where the source object has field that stores a collection of target objects. These target objects may also have an inverse relationship back to the source object. This relationship is established by having a foreign key in the target table that maps to the primary key of the source table.

An example of a one-to-many relationship is the relation between artist and art, where the artist has many artwork pieces.

A one-to-many relationship between two persistent components is defined using the fieldtype value one-to-many in the cfproperty tag. The source object contains a collection of target objects. ColdFusion allows the collection to be one of the following types:

Array
Struct

This collection is a persistence aware collection. Any addition or deletion from this collection is automatically persisted in the database.

Array

An Artist object can contain the Art objects as an array. To define this mapping in the CFC, use the following syntax:

Syntax

<cfproperty 
name="field_name" 
fieldtype="one-to-many" 
cfc="Referenced_CFC_name" 
fkcolumn="Foreign_Key_column" 
type="array" 
orderby="order_by_string" 
cascade="cascade_options" 
lazy="[true]|false|extra" 
fetch="join|[select]" 
inverse="true|[false]" 
batchsize="N" 
optimisticlock="[true]|false" 
readonly="true|[false]">

For the artist-art example, the relationship property in Artist.cfc is defined as follows:

<cfproperty name="art" type="array" fieldtype="one-to-many" cfc="Art" fkcolumn="ARTISTID">

type-array specifies that the artist object will contain art objects in an array.
fkcolumn="ArtistID" specifies that the foreign key column is ARTISTID that references the primary key of ARTIST table.

Struct

An Artist object can contain the Art objects as a struct. The key would be any column in the ART table (usually the primary key or a unique key). The value would be the Art object. To define this mapping, use the following syntax.

Syntax

<cfproperty 
name="field_name" 
fieldtype="one-to-many" 
cfc="Referenced_CFC_name" 
fkcolumn="Foreign_Key_column" 
type="struct" 
orderby="order_by_String" 
structkeycolumn = "Structure_Key_Column" 
structkeytype="ormtype" 
cascade="cascade_options" 
lazy="[true]|false|extra" 
fetch="join|[select]" 
inverse="true|[false]" 
batchsize="N" 
optimisticlock="[true]|false" 
readonly="true|[false]">

For the artist-art example, you can define the relationship property as:

<cfproperty name="art" type="struct" fieldtype="one-to-many" cfc="Art" fkcolumn="ARTISTID" structkeytype="int" structkeycolumn="ArtID">

type=struct specifies that the artist object will contain art objects in a struct.
structkeycolumn="ArtID" specifies that the key of the struct would be ArtID.

Note that ARTID is the primary key in Art table.
structkeytype="int" specifies the datatype of structkeycolumn.
fkcolumn="ArtistID" specifies that the foreign key column is ARTISTID that references the primary key of Artist table.

Many-to-one relationship

A many-to-one relationship is the inverse of a one-to-many relationship. In this relationship, many source objects can reference the same target object.

An example of this relationship is the relation between Art and Artist, where many Art are created by the same Artist. This relationship is established with the foreign key in the source table that references the primary key in the target table.

A many-to-one relationship between two persistent components is defined using the fieldtype value many-to-one in the cfproperty tag.

Syntax

<cfproperty 
name="fieldname" 
fieldtype="many-to-one" 
cfc="Referenced_CFC_Name" 
column="Foreign_Key_Column" 
mappedby="Mapped_Field_name_in_referenced_CFC" 
cascade="cascade_options" 
fetch="join|[select]" 
lazy="true|false" 
insert="[true]|false" 
update="[true]|false" 
optimisticlock="[true]|false" 
missingrowIgnored="true|[false]">

For the art-artist example, the relationship in the ART.cfc can be defined as:

<cfproperty name="artist" fieldtype="many-to-one" fkcolumn="artistid" cfc="Artist">

fkcolumn="ARTISTID" indicates that the foreign key column in Art table references the primary key ARTISTID of ARTIST table.

Many-to-many relationships

A many-to-many relationship is where the source objects contain a collection of target objects and the target objects in turn contain a collection of source objects.

An example of a many-to-many relationship is the relation between Order and Product, where an order has many products and a product has many orders.

This relationship is established by using a third table called a 'LinkTable'. The LinkTable contains the foreign key to both the tables participating in the relation. ORM looks for the map key column in they LinkTable and not the target table.

In the preceding example of Order-Product, a many-to-many relationship is established by using LinkTable.

A many-to-many relationship between two persistent CFCs is defined using the fieldtype="many-to-many" value in the cfproperty tag.

Note: If the fkcolumn name is not specified, ORM generates the fkcolumn name in the "#relationName#_ID" format.

Syntax

Order.cfc

<cfproperty 
name="fieldname" 
fieldtype="many-to-many" 
cfc="fully qualified name" 
linktable="Link table name" 
linkcatalog="Catalog for the link table" 
linkschema="Schema for the link table" 
fkcolumn="Foreign Key column name" 
inversejoincolumn="Column name " 
mappedby="Property in the target component that is referenced by the fk column in join table" 
type="[array]|struct" 
orderby="order by String 
structkeycolumn="The structure key column name" 
structkeydatatype="datatype". 
cascade="cascade options" inverse="true|[false]" lazy = "[true]|false" [optional] fetch="join|[select]" [optional] batchsize="integer" optimisticlock="[true]|false" readonly="true|[false]" 
missingrowIgnored="true|[false]">

For the Order-Product example, the many-to-many relationship is established using a third table "OrderProduct" that has two foreign keys: OrderId and ProductId. OrderId references the primary key orderId in the order table, and ProductId references the primary key productId in the Product table. This relationship can be defined as follows:

Order.cfc

<cfproperty name="products" fieldtype="many-to-many" CFC="Product" linktable="Order_Product" FKColumn="orderId" inversejoincolumn="productId" lazy="true" cascade="all" orderby="productId">

Product.cfc
```
<cfproperty name="orders" fieldtype="many-to-many" CFC="Order" linktable="Order_Product" FKColumn="productId" inversejoincolumn="orderId" lazy="true" cascade="all" orderby="orderId">
```
The fkcolumn here is the foreign key in the link table that references the primary key of the source table. InverseJoinColumn is the foreign key in the link table that references the primary key of the target table.