Problems with using criteria object to join 3 tables

[Thomason, Michael]

I have included the method that I'm using to retrieve data from a 3 table
join.  Initially I used the convience method
ClaimStagingPeer.doSelectJoinClaimStagingGroup() which resulted in a join
between the first 2 tables, the call to cs.getClaimStagingGroup() returned
the joined data from the initial retrieval, but the call to
cs.getClaimStagingCauses() resulted in a separate query, as expected.

For the next step I removed the call to the convience method and added the
addJoin() method calls in the listing below.  This results in the initial
select being the proper syntax for the 3 table join, but now both the
cs.getClaimStagingGroup() and cs.getClaimStagingCauses() result in
additional selects being issued.  I'm following the examples from the Torque
website, but am not able to get this to retrieve in a single SQL select
being issued.

Has anyone experienced this sort of problem?

Thanks,
Mike Thomason
704-344-4260



public void testSelectWithSQLJoin() throws Exception {

                Criteria crit = new Criteria() ;

                crit.add("CLAIM_STAGING.FK_STAGING_GROUP_ID", new
Integer(11), Criteria.EQUAL) ;
                // Add joins to criteria
                crit.addJoin(ClaimStagingPeer.FK_STAGING_GROUP_ID,
ClaimStagingGroupPeer.STAGING_GROUP_ID) ;
                crit.addJoin(ClaimStagingPeer.FK_STAGING_GROUP_ID,
ClaimStagingCausePeer.FK_STAGING_GROUP_ID) ;
                crit.addJoin(ClaimStagingPeer.STAGING_SEQUENCE_NO,
ClaimStagingCausePeer.FK_STAGING_SEQUENCE_NO) ;

                // The following results in the proper select statement with
all 3 tables being joined in the query.
                List codes = ClaimStagingPeer.doSelect(crit) ;

                Iterator i = codes.iterator() ;
                while(i.hasNext()) {
        
                        ClaimStaging cs = (ClaimStaging)i.next() ;
        
                        // Results in additional selects
                        ClaimStagingGroup sg = csl.getClaimStagingGroup() ;
                        String claimType = sg.getClaimType() ;
                        String gStat = sg.getGroupStatus() ;

                        // Also results in additional selects
                        List causes = cs.getClaimStagingCauses() ;
                        Iterator ii = causes.iterator() ;
                        while(ii.hasNext()) {
                                ClaimStagingCause sc =
(ClaimStagingCause)ii.next() ;
                                System.out.println(sc) ;
                        }

                }
        }

--
To unsubscribe, e-mail:   <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>