[ http://issues.apache.org/jira/browse/IBATIS-274?page=comments#action_12414036 ]
Tim Haley commented on IBATIS-274: ---------------------------------- Clinton, Yes, I had a feeling that the result would be something like this. I was hoping that you would be able to do some magic, but I can see your point. My only remaining comment is that you need to document this limitation in both the javadocs for the PaginatedList and the documentation for join mapping. Thank you for looking into this. Tim Haley > 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 > Assignee: Clinton Begin > > 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
