[ http://issues.apache.org/jira/browse/IBATIS-274?page=comments#action_12413139 ]
Dan Syrstad commented on IBATIS-274: ------------------------------------ I'm having the same problem with N+1 selects (groupBy). I believe that the desired behavior would be that N (N=pageSize) top-level objects are returned and each object contains the proper number of nested objects regardless of N. I can't see how any other behavior would be acceptable. The object graph is currently inconsistent depending on the page size, which is a UI setting. How the user wishes to view the objects should not affect the proper construction of the objects. I think Sven's comment about the behavoir being "undefined" may be true simply because there's no javadoc on PaginatedDataList (!). Also, Sven describes the technical problem, which should be fixed. This feature is exactly what I need, but since it does not work properly with "groupBy", I'll have to revert to queryForList(). Here's my mapping: <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd";> <sqlMap namespace="Load"> <resultMap id="LoadMap" class="com.kingcompanies.usps.fast.dao.Load" groupBy="loadNum"> <result property="loadNum" column="loadNum" /> <result property="carrierName" column="carrierName" /> <result property="carrierCode" column="carrierCode" /> <result property="departureDate" column="departureDate" /> <result property="trailerNum" column="trailerNum" /> <result property="grossWeight" column="grossWeight" /> <result property="stops" resultMap="Load.StopMap" /> </resultMap> <resultMap id="StopMap" class="com.kingcompanies.usps.fast.dao.Stop" groupBy="stopId" > <result property="stopId" column="stopId" /> <result property="stopNum" column="stopNum" /> <result property="stopDate" column="stopDate" /> <result property="stopTime" column="stopTime" /> <result property="consigneeName" column="consigneeName" /> <result property="consigneeEntryType" column="consigneeEntryType" /> <result property="shipments" resultMap="Load.ShipmentMap" /> <result property="appointment.apptNum" column="apptNum" /> <result property="appointment.apptDateTime" column="apptDateTime" /> <result property="appointment.apptStatus" column="apptStatus" /> <result property="appointment.truckArrivedDateTime" column="truckArrivedDateTime" /> <result property="appointment.unloadingStartedDateTime" column="unloadingStartedDateTime" /> <result property="appointment.unloadingCompletedDateTime" column="unloadingCompletedDateTime" /> </resultMap> <resultMap id="ShipmentMap" class="com.kingcompanies.usps.fast.dao.Shipment"> <result property="proNum" column="proNum" /> <result property="description" column="description" /> <result property="loadClass" column="loadClass" /> <result property="mailType" column="mailType" /> <result property="jobNum" column="jobNum" /> <result property="jobName" column="jobName" /> <result property="quantity" column="quantity" /> <result property="uom" column="uom" /> <result property="shipmentWeight" column="shipmentWeight" /> <result property="pieces" column="pieces" /> <result property="deliveryDate" column="deliveryDate" /> </resultMap> <select id="getLoads" parameterClass="com.kingcompanies.usps.fast.dao.LoadQueryParams" resultMap="LoadMap"> SELECT Loads.LOAD_NUM loadNum, Loads.Carrier_Name carrierName, Loads.CARR_CODE carrierCode, Loads.Departure_Date departureDate, tblAvailablePros.fldTrailerNum trailerNum, Loads.Gross_Weight grossWeight, tblAvailStopCons.fldLoadStopId stopId, tblAvailStopCons.fldStopNum stopNum, tblAvailStopCons.fldApptDate stopDate, tblAvailStopCons.fldApptTime stopTime, Consignees.CONSIGNEE consigneeName, Consignees.fldEntryType consigneeEntryType, tblAvailablePros.fldProNum proNum, tblAvailablePros.fldComments description, tblAvailablePros.fldClass loadClass, tblAvailablePros.fldMailType mailType, tblAvailablePros.fldJobNum jobNum, tblAvailablePros.fldJobName jobName, tblAvailablePros.fldQuan quantity, tblAvailablePros.fldUOM uom, tblAvailablePros.fldWeight shipmentWeight, tblAvailablePros.fldPieces pieces, tblAvailablePros.fldDelDate deliveryDate, tblUSPSAppointments.fldApptNum apptNum, tblUSPSAppointments.fldApptDateTime apptDateTime, tblUSPSAppointments.fldStatus apptStatus, tblUSPSAppointments.fldTruckArrived truckArrivedDateTime, tblUSPSAppointments.fldUnloadingStarted unloadingStartedDateTime, tblUSPSAppointments.fldUnloadingCompleted unloadingCompletedDateTime FROM Loads INNER JOIN tblAvailStopCons ON Loads.LOAD_NUM = tblAvailStopCons.fldLoadNum LEFT OUTER JOIN tblUSPSAppointments ON Loads.LOAD_NUM = tblUSPSAppointments.fldLoadNum AND tblAvailStopCons.fldConsId = tblUSPSAppointments.fldConsId INNER JOIN tblAvailablePros ON Loads.LOAD_NUM = tblAvailablePros.fldLoadNum AND tblAvailStopCons.fldStopNum = tblAvailablePros.fldStopNum INNER JOIN Consignees ON tblAvailStopCons.fldConsId = Consignees.Cons_ID WHERE (Consignees.fldEntryType = 'SCF' OR Consignees.fldEntryType = 'BMC' OR Consignees.fldEntryType = 'ASF') AND Loads.Gross_Weight BETWEEN #startWeight# AND #endWeight# AND Loads.Departure_Date BETWEEN #startDate# AND #endDate# AND Loads.CARR_CODE IS NOT NULL AND tblAvailStopCons.fldApptDate IS NOT NULL AND tblAvailStopCons.fldApptTime IS NOT NULL AND ( (#wantScheduled#=1 AND tblUSPSAppointments.fldApptDateTime IS NOT NULL) OR (#wantUnscheduled#=1 AND tblUSPSAppointments.fldApptDateTime IS NULL) OR (#wantClosedOut#=1 AND tblUSPSAppointments.fldUnloadingCompleted IS NOT NULL) ) ORDER BY Loads.Departure_Date, Loads.LOAD_NUM, tblAvailStopCons.fldStopNum </select> </sqlMap> > queryForPaginatedList doesn't play well with groupBy attribute of resultMap > (N+1 selects solution) > --------------------------------------------------------------------------------------------------- > > Key: IBATIS-274 > URL: http://issues.apache.org/jira/browse/IBATIS-274 > Project: iBatis for Java > Type: Bug > Components: SQL Maps > Versions: 2.1.7 > Environment: WinXP Pro, WebLogic 8.1, JDK 1.4.2_05, Spring 1.2.7 > Reporter: Tim Haley > > I have implemented the N+1 solution using the groupBy attribute of the > resultMap. > When this is called using queryForList() it works as expected; I get two > BatchResults objects, the first contains a list of 20 ProcessingResult > objects and the second contains a list of 19 ProcessingResult objects. > When called with queryForPaginatedList() and a page size of 10, I only get > the first BatchResults object, which does contain the list of 20 > ProcessingResult objects. > When called with queryForPaginatedList() and a page size of 19, I only get > both BatchResults objects, the first contains a list of 20 ProcessingResult > objects and the second contains a list of 18 ProcessingResult objects. (total > of 38, 2x the page size) > When called with queryForPaginatedList() and a page size of 18, I only get > both BatchResults objects, the first contains a list of 20 ProcessingResult > objects and the second contains a list of 16 ProcessingResult objects. (total > of 36, 2x the page size) > When called with queryForPaginatedList() and a page size of 17, I only get > both BatchResults objects, the first contains a list of 20 ProcessingResult > objects and the second contains a list of 14 ProcessingResult objects. (total > of 34, 2x the page size) > See details of mapping and method calls below: > <resultMap id="BatchResultMap" class="BatchResult" > groupBy="processingSchedule.processingKey"> > <result property="processingSchedule.processingKey" > column="PROCESSING_KEY" javaType="int"/> > <result property="processingSchedule.processingDate" > column="PROCESS_DATE" javaType="date"/> > <result property="processingSchedule.printDate" column="PRINT_DATE" > javaType="date"/> > <result property="processingSchedule.processType.processTypeKey" > column="PROCESS_TYPE_KEY" javaType="int"/> > <result property="processingSchedule.processType.description" > column="PROCESS_TYPE_DESCRIPTION" javaType="string"/> > <result property="processingResults" > resultMap="Processing.ProcessResultMap"/> > </resultMap> > <resultMap id="ProcessResultMap" class="ProcessingResult"> > <!--<result property="processingKey" column="PR_PROCESSING_KEY"/>--> > <result property="applicationKey" column="APPLICATION_KEY" > javaType="int"/> > <result property="applicationStatusKey" > column="APPLICATION_STATUS_KEY" javaType="int"/> > <result property="applicationStatusString" > column="APPLICATION_STATUS_DESCRIPTION" javaType="string"/> > <result property="testResults" > column="{applicationKey=APPLICATION_KEY, processingKey=PR_PROCESSING_KEY}" > select="getTestResultsForProcessingResult"/> > <result property="application" column="APPLICATION_KEY" javaType="int" > select="loadApplicationByPrimaryKey"/> > <result property="processing" column="PR_PROCESSING_KEY" > javaType="int" > select="loadProcessingSchedule"/> > </resultMap> > <select id="loadBatchResultsForDateRange" parameterClass="DateRange" > resultMap="BatchResultMap"> > select > PRO.PROCESSING_KEY, > PRO.PROCESS_DATE, > PRO.PRINT_DATE, > PRO.PROCESS_TYPE_KEY, > PT.DESCRIPTION as PROCESS_TYPE_DESCRIPTION, > PR.APPLICATION_KEY, > ASCD.APPLICATION_STATUS_KEY, > ASCD.DESCRIPTION as APPLICATION_STATUS_DESCRIPTION, > PR.PROCESSING_KEY as PR_PROCESSING_KEY > from > WM.USTP_PROCESSING PRO > join WM.USTP_PROCESS_TYPE_CD PT on PRO.PROCESS_TYPE_KEY = > PT.PROCESS_TYPE_KEY > left outer join WM.USTP_PROCESSING_RESULT PR on PR.PROCESSING_KEY > = PRO.PROCESSING_KEY > left outer join WM.USTP_APPLICATION_STATUS_CD ASCD on > ASCD.APPLICATION_STATUS_KEY = PR.APPLICATION_STATUS_KEY > where > PRO.PROCESS_DATE between #start# and (#end# + 1) > order by > PRO.PROCESSING_KEY desc > </select> > public List loadByProcessingDateRange(DateRange aDateRange) > throws DataAccessException > { > return > getSqlMapClientTemplate().queryForList("loadBatchResultsForDateRange", > aDateRange); > } > public PaginatedList loadByProcessingDateRange(DateRange aDateRange, > int aPageSize) > throws DataAccessException > { > return > getSqlMapClientTemplate().queryForPaginatedList("loadBatchResultsForDateRange", > aDateRange, > aPageSize); > } -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
