tracker issue : CF-3041659

Problem:

When using eager fetching (fetch="join") for an entity with a one-to-many collection, and the entity is retrieved using entityLoad() with filter criteria, then the result is that the return array from entityLoad() has the parent object repeated *for each child object* in the one-to-many collection.For example,  say we have a single entity named 'Parent', holding 5 'Child' objects as a one-to-many collection.  If this 'Parent' is retrieved using entityLoad( 'Parent', { name = 'Parent 1' } ), then the result is that we get an array of 5 'Parent's, even though there is only one 'Parent' object, with 5 children ('Child' objects).If the fetch="join" attribute is taken away (and lazy loading is used in its place), then the entityLoad() call runs as expected.The incorrect behavior is also exhibited when running ORMExecuteQuery() when LEFT JOIN FETCH'ing the one-to-many relationship.  I included both examples in my sample code.  Be sure to check the comments in the test file (index.cfm) file for more information.
Method:

Simply run this example app from a single directory.  I also included a SQL Server script to run to create the database.Parent.cfc:<cfcomponent displayName="Parent" output="no" persistent="true" table="Parents" entityName="Parent"><cfproperty name="parentID" fieldType="id" generator="native"><cfproperty name="name" type="string"><cfproperty name="children" fieldType="one-to-many" cfc="Child" fetch="join" cascade="all-delete-orphan" orderBy="name"></cfcomponent>Child.cfc:<cfcomponent displayName="Child" output="no" persistent="true" table="Children" entityName="Child"><cfproperty name="childID" fieldType="id" generator="native"><cfproperty name="name" type="string"></cfcomponent>index.cfm:<cfset ORMReload()><!--- Comes out correctly, with one Parent, and 5 Child Items.HOWEVER, this entityLoad() is not actually honoring the fetch="join" attribute inthe cfproperty tag. It's running 2 queries: one for parents, and one for the children. ---><cfset parents = entityLoad( 'Parent' )>Correct Parent Dump: <cfdump var="#parents#"><cfoutput>Num Parents: #arrayLen( parents )#</cfoutput><!--- Outputs "1" ---><br><hr><br><br><!--- With entityLoad() filter parameters, I am receiving an array of 5 Parent'sbecause of the fetch="join". They all refer to the same actual Parent entity,but I really should only be receiving 1 Parent object, which holds 5Child objects. ---><cfset parents = entityLoad( 'Parent', { name = 'Parent 1' } )>Incorrect Parent Dump: <cfdump var="#parents#"><cfoutput>Num Parents: #arrayLen( parents )#</cfoutput><!--- Outputs "5" ---><br><hr><br><br><!--- A test with ORMExecuteQuery. Same behavior as entityLoad() with parameters. ---><cfset parents = ORMExecuteQuery( "FROM Parent parentLEFT JOIN FETCH parent.childrenWHERE parent.name = 'Parent 1'" )>Incorrect Parent Dump using ORMExecuteQuery(): <cfdump var="#parents#"><cfoutput>Num Parents: #arrayLen( parents )#</cfoutput><!--- Outputs "5" --->SQL Server script to quickly create the database:IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'dbo' AND table_name = 'children' )DROP TABLE [dbo].[children]IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'dbo' AND table_name = 'parents' )DROP TABLE [dbo].[parents]CREATE TABLE [dbo].[parents] ([parentID] [int] IDENTITY(1,1) NOT NULL,[name] [varchar](50) NOT NULL,CONSTRAINT [PK_parents] PRIMARY KEY CLUSTERED ( [parentID] ASC ))GOCREATE TABLE [dbo].[children] ([childID] [int] IDENTITY(1,1) NOT NULL,[parentID] [int] NULL,[name] [varchar](50) NOT NULL,CONSTRAINT [PK_children] PRIMARY KEY CLUSTERED ( [childID] ASC ))GOALTER TABLE [dbo].[children] WITH CHECK ADD CONSTRAINT [FK_children_parents] FOREIGN KEY( [parentID] )REFERENCES [dbo].[parents] ( [parentID] ) ON UPDATE CASCADE ON DELETE CASCADEGOALTER TABLE [dbo].[children] CHECK CONSTRAINT [FK_children_parents]-- ----------------------------------------------DECLARE @parentID INTINSERT INTO [dbo].[parents] ( [name] ) VALUES ( 'Parent 1' )SELECT @parentID = @@IDENTITYINSERT INTO [dbo].[children] ( [parentID], [name] ) VALUES ( @parentID, 'Child 1' )INSERT INTO [dbo].[children] ( [parentID], [name] ) VALUES ( @parentID, 'Child 2' )INSERT INTO [dbo].[children] ( [parentID], [name] ) VALUES ( @parentID, 'Child 3' )INSERT INTO [dbo].[children] ( [parentID], [name] ) VALUES ( @parentID, 'Child 4' )INSERT INTO [dbo].[children] ( [parentID], [name] ) VALUES ( @parentID, 'Child 5' )
Result:

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3041659

External Customer Info:
External Company:  
External Customer Name: Gjslick Gjslick
External Customer Email: 11CE679F445E352D9920157F
External Test Config: 06/15/2010

Attachments:

Comments: