ColdFusion-ORM: Define One-to-Many and Many-to-one relationships

• Author: Manjukiran
• July 15, 2009
• Category: ColdFusion, ORM

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>

• 21 Responses
• Leave a comment...
• Comment Feeds