[jira] Commented: (IBATIS-79) Problem with nested groupBy resultMaps

["raja chawat (JIRA)" <ib...@incubator.apache.org>]

    [ https://issues.apache.org/jira/browse/IBATIS-79?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12495991 ] 

raja chawat commented on IBATIS-79:
-----------------------------------

How do we add per-resultMap uniqueKeys? I don't see any example.
Please help. 

My resultset is like: 
I have a list of instructors in a group, and every instructor can teach a list of courses. My problem is an instructor can belong to multiple groups. 
I see instructor only in 1 group. And I see list of components repeated for that instructor for every group that he/she belongs to.

And if I remove the 2nd groupBy, I see instructor in multiple groups. But in each group, it is listed n number of times, where n is the number of courses he/she can teach.

Am I doing something wrong here, please comment. How do I resolve this issue?

Also, the regionID can be same as groupByID, since I am grouping dynamically. I have "select region_id as group_by_id".

    <resultMap id="GroupVOXResultMap" class="GroupVOX" groupBy="groupByID">
        <result property="groupByID" column="group_by_id"/>
        <result property="groupByDesc" column="group_by_desc"/>
        <result property="instructorVOXList" resultMap="Scheduler.InstructorVOXResultMap"/>
    </resultMap>
    <resultMap id="InstructorVOXResultMap" class="InstructorVOX" groupBy="instructorID">
        <result property="instructorID" column="inst_id"/>
        <result property="firstName" column="fname"/>
        <result property="middleInitial" column="mi"/>
        <result property="lastName" column="lname"/>
        <result property="regionID" column="region_id"/>
        <result property="regionDesc" column="region_desc"/>
        <result property="cpntPKs" resultMap="Scheduler.ComponentPKResultMap"/>
    </resultMap>
    <resultMap id="ComponentPKResultMap" class="ComponentPK">
        <result property="componentTypeID" column="cpnt_typ_id"/>
        <result property="componentID" column="cpnt_id"/>
        <result property="revisionDate" column="rev_dte"/>
    </resultMap>

> Problem with nested groupBy resultMaps
> --------------------------------------
>
>                 Key: IBATIS-79
>                 URL: https://issues.apache.org/jira/browse/IBATIS-79
>             Project: iBatis for Java
>          Issue Type: Bug
>          Components: SQL Maps
>    Affects Versions: 2.0.9b
>         Environment: iBATIS 2.0.9b.550
> java 1.4.2_05
> Mac OS X 10.3.8
> MySQL 4.1.7
>            Reporter: Stuart Piltch
>         Assigned To: Clinton Begin
>             Fix For: 2.1.0
>
>
> (originally posted on ibatis-user-java)
> I've been working on a query that involves two levels of groupBy,
> similar to the JPetStore-ish example from the UnitTests that Clinton
> posted on the mailing list a while ago
> (http://article.gmane.org/gmane.comp.java.ibatisdb.user/383).
> It was working most of the time, but would fail under certain
> cirumstances. After way too many dead-ends, I found out that if the
> groupBy from the first resultMap has the same value as the groupBy
> from the second resultMap, iBATIS gets a bit confused and tries to
> assign the second result to a property from the first class. Since
> that makes almost no sense now that I see it in print, let me
> copy/paste some examples. The goal is a report that shows "topics" as
> rows (grouped into categories), with "months" as columns and the total
> topics displayed per month in the table itself.
>    <resultMap id="reportResult" class="Category" groupBy="categoryName">
>      <result property="categoryName" column="topic_category_name" />
>      <result property="topics" resultMap="Report.topicResult" />
>    </resultMap>
>    <resultMap id="topicResult" class="Topic" groupBy="topicName">
>      <result property="topicName" column="topic_name" />
>      <result property="months" resultMap="Report.monthResult" />
>    </resultMap>
>    <resultMap id="monthResult" class="Month">
>      <result property="monthName" column="month_name" />
>      <result property="count" column="count" />
>    </resultMap>
>   <select id="getTopicsByMonth" parameterClass="Report" resultMap="reportResult">
>     SELECT tc.topic_category_name,
>            t.topic_name,
>            CONCAT(MONTHNAME(td.display_date), ' ', YEAR(td.display_date)) AS 'month_name',
>            COUNT(td.topics_displayed_id) AS 'count'
>       FROM topic t,
>            topic_category tc,
>            topics_displayed td
>      WHERE t.topic_id = td.topic_id
>        AND t.topic_category_id = tc.topic_category_id
>        AND td.display_date >= #startDate#
>        AND td.display_date <= #endDate#
>      GROUP BY 'category', 'row', 'column'
>      ORDER BY tc.sort_order,t.sort_order, YEAR(td.display_date),
> MONTH(td.display_date)
>   </select>
> Now, everything works perfectly *except* when the topic_category_name
> happens to equal the topic_name. In that case, iBATIS fails with:
> com.ibatis.common.beans.ProbeException: There is no READABLE property named 'months' in class '[...snip...].Category'
> at com.ibatis.common.beans.ClassInfo.getGetter(ClassInfo.java:160)
> at com.ibatis.common.beans.JavaBeanProbe.getProperty(JavaBeanProbe.java:263)
> at com.ibatis.common.beans.JavaBeanProbe.getObject(JavaBeanProbe.java:252)
> at com.ibatis.common.beans.GenericProbe.getObject(GenericProbe.java:55)
> at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.
> setNestedResultMappingValue(BasicResultMap.java:334)
> [...let me know if you really want to see the rest...]
> So, for some reason, when iBATIS is reading the months counts for this
> particular topic, it tries to save the months in Category instead of
> in Topic. As soon as I changed the one topic_name in the database,
> everything worked fine.
> From a quick step-through of the code, my first guess is that the
> problem is with the single uniqueKeys map per request (used in the
> BasicResultMap.setResultObjectValues() method.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.