ColdFusion-ORM: Define One-to-Many and Many-to-one relationships
Task:
Example to demonstrate how to establish one-to-many and many-to-one Relationships in ColdFusion-ORM
Previous Related Posts:
Getting Started with ORM
ColdFusion-ORM: Using CRUD Functions
Stuff that you would learn:
- how to establish a One-to-Many relationship between 2 CFCs.
- how to establish a Many-to-One relationship between 2 CFCs.
- how to do CRUD operations with CFCs which have a relationship.
- how to use the implicit relationship methods added by ColdFusion.
- cascade insert and delete
Steps to Run the example:
- This example needs the cfartgallery datasource. This is shipped with ColdFusion by default.
- Create a directory say "1tonorm" under webroot.
- Create the following files – Application.cfc, Art.cfc, Artists.cfc and index.cfm.
- Run the URL http://localhost:8500/1tonorm/index.cfm
I have interspersed the example with a lot of comments. You can understand the concept by just following the comments starting with Application.cfc and then Artists.cfc and then Arts.cfc and then index.cfm.
Application.cfc
component
{
//Name of the application
this.name = "ORM_One2Many";
//ormenabled should be set to true so that ORM is enabled for this application
this.ormenabled = "true";
//Set the datasource that needs to be used by the ORM Functions.
this.datasource = "cfartgallery";
}
Artists.cfc
component persistent="true"
{
property name="artistid" generator="increment";
property firstname;
property lastname;
property address;
property city;
property state;
property postalcode;
property email;
property phone;
property fax;
property thepassword;
/*
Artists have many arts and henace they form a one-to-many relation. The idea is to have all
the arts as an array of art objects in each Artists' object.
Set fieldytpe="one-to-many".
Specify CFC="Art" to convey that the relationship is with Art.cfc.
fkcolumn="artistid" to specify the foreign key. But this is not required if foreignkey constraint
is defined in the database - ColdFusion-ORM will figure it out by inspecting the database.
cascade="all-delete-orphan": "all" signifies that all the CRUD operations will be cascaded to the
related objects. "delete-orphan" means that it will delete the orphan objects in the art array
i.e. if i remove an art from the arts array, it is now an orphan as it has no artist associated
with it and hence will be automatically deleted.
Implit Methods: When a one-to-many relationship is established, the following implicit methods are introduced
into the artist object:
void addart(<artobj>) - used to add a art object to the relationship.
boolean removeart(<artobj>) - used to remove the art object from the relationship.
boolean hasart() - to check if there are any art objects in the artist object.
boolean hasart(<artobj>) - to check if the input art object is present in the artist object.
singularname: Use this attribute to specify custom name to the above implicit methods.
type=array: To retrieve the arts objects as an array. If type="struct", then the arts objects are retrieved as
key-value pairs. structkeycolumn and structkeytype should be specified in addition to all the above attributes
to retrieve arts as key-value pairs.
inverse=true: In a bi-directional relationship like this, you should specify one of the side as the controlling side
which will set the relationship. Usually, in a one-to-many bi-directional relationship, the many-to-one side should
be the controlling side. To do that, set inverse=true on this side (one-to-many side).
*/
property name="arts" type="array" fieldtype="one-to-many" cfc="Art" singularname="art" fkcolumn="artistid"
inverse="true" cascade="all-delete-orphan";
}
Art.cfc
component persistent="true"
{
property name="artid" generator="increment";
property name="artname";
property name="price";
property name="largeimage";
property name="mediaid";
property name="issold";
/*
Many arts have an artist and hence form a "many-to-one" relation.
To establish a "many-to-one" relation, set fieldytpe="many-to-one"
Specify CFC="Artists" to convey that the relationship is with Artists.cfc.
fkcolumn="artistid": Used to specify the foreign key.
missingRowIgnored: If the value is true, and the row that is referenced by the foreign
key is missing, it is treated as a null association.
*/
property name="artist" fieldtype="many-to-one" fkcolumn="artistid"
cfc="Artists" missingRowIgnored="true";
}
index.cfm
<!---
This example will teach you
- how to establish a One-to-Many relationship between 2 CFCs.
- how to establish a Many-to-One relationship between 2 CFCs.
- how to do CRUD operations with CFCs which have a relationship.
- how to use the implicit relationship methods added by ColdFusion.
- Using cascade in one-to-many to many-to-one relationships.
cfartgallery datasource is used for this application.
--->
<cfscript>
ormreload();
/*
Display the existing records in Artists Table.
This will display the artist and their arts.
*/
WriteOutput("<b>Initial state of the Artists and Art tables<br></b>");
DisplayArtists();
/*
Create an artist object and 2 art objects
*/
newArtist = new Artists();
newArtist.setfirstname("Daniel");
newArtist.setlastname("Richard");
newArt1 = new Art();
newArt1.setArtName("Champions");
newArt2 = new Art();
newArt2.setArtName("Isolate");
/*
Associate the Arts to the Artist. Notice that the
implicit-relationship method - addArt is called.
*/
newArtist.addArt(newArt1);
newartist.addArt(newArt2);
/*
The one-to-many relationship should be established from both
the ends. Hence set the Artist to the new Arts.
*/
newArt1.setArtist(newArtist);
newArt2.setArtist(newArtist);
/*
Save the new artist. Note that EntitySave will save the artist as
well as the associated arts as well (cascade-insert)
*/
EntitySave(newArtist);
ormflush();
/*
Display the existing records to check whether insert succeeded.
*/
WriteOutput("<b>State of the Artists and Art tables after insert. Notice that the new
artist - Daniel Richard is added with his arts 'Champions' and 'Isolate'<br></b>");
DisplayArtists();
/*
Remove newArt2 and add newArt3. In a way, you are just modifying the 'arts' array. Notice that
we are using the implicit=relationship method - removeArt is called.
*/
newArt3 = new Art();
newArt3.setArtName("Holiday");
newArt3.setArtist(newArtist);
newArtist.addArt(newArt3);
newArtist.removeArt(newArt2);
ormflush();
/*
Check if delete and update of art succeeded.
*/
WriteOutput("<b>State of the Artists and Art tables after Deleting/Updating his Arts. Notice that
the art 'Isolate' is removed and the art 'Holiday' is added.<br></b>");
DisplayArtists();
/*
Delete the Artist. Note that EntityDelete will delete
the Artist as well as the associated arts. (cascade-delete).
*/
EntityDelete(newArtist);
ormflush();
/*
Check if delete succeeded.
*/
WriteOutput("<b>State of the Artists and Art tables after deleting the Artist.
Notice that the artist 'Daniel Richard' is deleted. All his arts are also deleted!<br></b>");
DisplayArtists();
/*
A Utility function to display artists and his arts
*/
function DisplayArtists()
{
query1 = new Query();
query1.setSQL("select ArtistID, firstname, lastname from Artists where ArtistID>10");
WriteDump(query1.execute().getresult());
query1.setSQL("select ArtID, ArtName, ArtistID from Art where ArtID>50");
WriteDump(query1.execute().getresult());
}
</cfscript>
Comments
Hi Manju. Great series of posts! I have a question. In your index.cfm file you say that “The one-to-many relationship should be established from both the ends.” Why do you need to this? If you associate from one side, doesn’t hibernate know about the relationship?
Hi John, hibernate would know it. But, the calls newArt1.setArtist(newArtist) and newArt2.setArtist(newArtist); wouldn’t be automatic. The newArt1 and newArt2 objects would continue to have Artist=NULL and would be out-of-sync with the db.
Manju, I really appreciate this post. The livedocs are great, but their examples are very simplistic, and it is difficult to get the full picture from them. Having a complete example here is very helpful.
Nice example usage
One question though…
Is there any reason why you are calling DisplayArtists like this:
DisplayArtists(EntityLoad(”Artists”));
when the DisplayArtists function itself doesn’t accept or use any arguments? Shouldn’t you just be calling DisplayArtists() each time instead?
@Neil, thanks for the pat!
@Justin, good catch. DisplayArtists initially used the objects to display the data but later i had changed it to fetch data using SQL – missed to update the calls. Will correct it now
This is interesting but basic. Do you have a tutorial or know of one showing how to do something like say a pivot table?
For instance say if you extend this out so that you have art galleries and you want to have a table that connects artists to galleries. Each artist could have art in more than 1 gallery. Thus the pivot table.
I have this exact scenario but with volunteers. They could volunteer with more than one organization so I have a table that keeps track of volunteer-organization.
How would I do that with ORM?
For me the example doesn’t work, the Art records are never added.
Maybe something changed in the ColdFusion version 9,0,0,251028 of ORM?
I’ll have a look on the Adobe website.
Otherwise I have been enjoying your tutorials, they are very helpfull! Thanks!
Ok remove my post please, seems i had a case of cache.
I was lazy and didn’t create a new directory, rather i just overwrite the files from previous tutorials.
The tutorial is working fine!
Write a Comment