CF9 ORM Replicate Left Outer Join

Question

Hi there

I am using the ORM features in CF9 and am just a bit stuck on how to replicate a left outer join query..

I have a TaskList CFC

component output="false" persistent="true"{
property name="iTaskListID" fieldtype="id" generator="native";
property name="sTitle" ormtype="string" length="50";
property name="task" fieldtype="one-to-many" cfc="Task" fkcolumn="iTaskListID"
;
}

And a Task CFC

component output="false" persistent="true"{

property name="iTaskID" fieldType="id" generator="native"; property name="sTitle" notnull="true" type="string"; property name="dtCreated" ormtype="date"; property name="iListingID" ormtype="integer"; property name="User" fieldtype="many-to-one" cfc="Users" fkcolumn="iUserID"; property name="iTaskListID" ormtype="integer"; }

I then perform an entityLoad

 <cfset results = entityLoad("TaskList",url.iTaskListID,true)>
 <cfset resultsQuery = entityToQuery(results)>

However, when I try and use the resultsQuery, I cannot access the task cfc properties. For example

cfoutput>
 <cfloop query="resultsQuery">

#resultsQuery.iTaskID#

Will produce the error Element ITASKID is undefined in RESULTSQUERY.

Would anyone have any advice?

Many thanks

Answer 1

entityToQuery does not integrate the relationship properties, only the native properties within the Hibernate entity loaded. Do a and you'll see only the iTaskListID and sTitle fields in the query (per your example).

There is no reason to convert the entityLoad output (one object, since you are passing in ID and unique=true) into a query. Access the object directly in your results var, and access the relationship by looping through output of results.getTask(), which will output an array of Task objects (TaskList has a one-to-many relationship to Task).

<cfloop array="#results.getTask()#" index="obj">
   #obj.getITaskID()#
</cfloop>

Not that I recommend it, but if you really want to access this via query methods, call this instead:

<cfset taskQuery = entityToQuery(results.getTask())>
<cfoutput query="taskQuery">
    #taskQuery.iTaskID#
</cfoutput>