Define 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

You can also use the link table to establish a relationship. A link table contains the foreign key to both tables that participate in the relationship. ORM looks for the map key column using the link table and not the target table.

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.

If the relationship is established using multiple foreign key columns (that reference the composite key of the source table), then you must use comma-separated column names.Also, the order in which the column names are specified must match the order of composite keys defined. If you do not specify any values, then

  • If the table exists and the constraints are defined, ColdFusion automatically selects the values from the table

  • If the table does not exist, ColdFusion auto-generates the values

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.

In case of a composite key, you can use a comma-separated list of column names.

If the join table has multiple foreign key columns (that reference the composite key of the target table), then you must use comma-separated column names.Also, the order in which the column names are specified must match the order of composite keys defined.If you do not specify any values, then
  • If the table exists and the constraints are defined, then ColdFusion automatically selects the values from the table

  • If the table does not exist, then ColdFusion auto-generates the values

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,

(in ColdFusion 9.0.1) one-to-one

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" 
linktable="Link table name" 
linkcatalog="Catalog for the link table" 
linkschema="Schema for the link table" 
fkcolumn="Foreign Key column name" 
inversejoincolumn="Column name or comma-separated list of primary key columns" 
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'}" ormtype="int"> 
        <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" 
linktable="Link table name" 
linkcatalog="Catalog for the link table" 
linkschema="Schema for the link table" 
fkcolumn="Foreign Key column name" 
inversejoincolumn="Column name or comma-separated list of primary key columns" 
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" 
linktable="Link table name" 
linkcatalog="Catalog for the link table" 
linkschema="Schema for the link table" 
fkcolumn="Foreign Key column name" 
inversejoincolumn="Column name or comma-separated list of primary key columns " 
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" 
linktable="Link table name" 
linkcatalog="Catalog for the link table" 
linkschema="Schema for the link table" 
fkcolumn="Foreign Key column name" 
inversejoincolumn="Column name or comma-separated list of primary key columns" 
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" 
linktable="Link table name" 
linkcatalog="Catalog for the link table" 
linkschema="Schema for the link table" 
fkcolumn="Foreign Key column name" 
inversejoincolumn="Column name or comma-separated list of primary key columns" 
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 the 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 or a composite key with comma-separated primary key columns" 
mappedby="Property in the target component that is referenced by fkcolumn 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. This attribute can also take a composite key value, for example you can specify inversejoincolumn=”field1, field2”, where field1 and field2 form the composite key.