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-manymany-to-many | Optional | An integer value that specifies the " batchsize " for fetching uninitialized collections. For details, see Batch fetching in Lazy Loading. | |
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: truefalseSee One-to-one relationships below 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 selectSee Lazy Loading for details. |
fieldtype | all | Required | column | Specifies the type of relationship mapping:one-to- oneone -to- manymany -to- onemany -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
|
|
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- manymany -to-many | Optional | false | Specifies whether SQL query should be executed for this association when persisting this object. Value are : truefalse 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
|
|
lazy | all | Optional | true | Specifies if the association should be loaded lazily. truefalseextraSee 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. Note that the value of this property is case-sensitive. | |
missingrowIgnored | many-to-one, many-to-many, (in ColdFusion 9.0.1) one-to-one | Optional | false | Values are: truefalseIf 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: truefalseSee Optimistic locking for details. |
orderby | one-to- manymany -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-manymany-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- manymany -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- manymany -to-manytype=struct | optional | The column in the target table to use as key if the collection type is struct . | |
structkeytype | one-to- manymany -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- manymany -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:truefalseSet 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- manymany -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">
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 turncontain 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.
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.