1 Reply Latest reply on Nov 2, 2010 12:25 PM by cflenster

    ORM EXPERTS: How to insert a record into a linking table

    cflenster

      I have table called TrainingArchives and a table called TrainingCategories. They are both modeled in ORM and both have a FK property referencing a linking table.

       

      This is the assignedCategory property in  TrainingArchive.cfc (ORM object):

       

      property name="assignedCategory" type="array" displayname="assignedCategory" hint="This is the assigned categories for the training archive."

      fieldtype="many-to-many" linktable="trainingArchiveCategory" FKColumn="trainingArchiveID" inversejoincolumn="trainingCategoryID" cfc="TrainingCategory"

      orderby="trainingCategoryID";

       

      This is the assignedTrainingArchive property in TraningCategory.cfc (ORM object)

       

      property name="assignedTrainingArchive" type="array" displayname="assignedTrainingArchive" hint="This is the assigned training archives for the topic."

      fieldtype="many-to-many" linktable="trainingArchiveCategory" FKColumn="trainingCategoryID" inversejoincolumn="trainingArchiveID" cfc="TrainingArchive"

      orderby="trainingArchiveID";

       

      If I prepopulate the linking table with data, I am able to use ORM and Hibernate to get the data from the linking table. However, I don't know how to INSERT a record into the linking table using ORM. So, I have a form where a user can enter data to create a training archive record, one of the fields is a select element where the user can select multiple categories. When the user hits the 'Save' button, I can easily save the data by using the built-in set methods on the other properties of the training archive object. But I don't know how to get the data into the assignedCategory property. I've tried just passing in the selected values as an array but that doesn't work as in setAssignedCategory(myArray).

       

      I'd be happy to provide more info if necessary but I think you get the gist of what I'm asking.

       

      Thanks!

        • 1. Re: ORM EXPERTS: How to insert a record into a linking table
          cflenster Level 1

          I will answer my own question thanks to this article:

          http://www.adobe.com/devnet/coldfusion/articles/coldfusion9_orm.html

           

          I added the singularname attribute to my assignedCategories property. This allows me to access methods called addAssignedCategory() and removeAssignedCategory (built-in methods created for many-to-many relationships).

           

          Here is the code I used:

          //to set the assigned categories property we need to first

          //loop over the list of selected category id's

          <cfloop list="#rc.trainingCategoryID#" index="i">  //trainingCategoryID is a list of id's selected from the categories dropdown box in my form

               <!---load the category objects--->

               <cfset catObject = EntityLoad("TrainingCategory",#i#,true)>

               <!--- now add the category object to the training archive object--remove it first so there aren't duplicate entries --->

               <cfset obj.removeAssignedCategory(catObject)> //earlier in my function a defined obj as the training archive ORM object that is being updated

               <cfset obj.addAssignedCategory(catObject)>

          </cfloop>

           

          after all the properties of my training archive object have been set I then call

           

          obj.save()

           

          This will update/insert the training archive data in the db

           

          AND it will also automagically insert the appropriate data in the trainingArchiveCategories linking table

           

          HUGE THANK YOU TO

          Mark Mandel

          (author of afore-mentioned article)