You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Stuart Piltch <st...@gradientblue.com> on 2005/02/21 23:32:13 UTC

Problem with nested groupBy resultMaps

Hi there,

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 here 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.

Re: Problem with nested groupBy resultMaps

Posted by Clinton Begin <cl...@gmail.com>.
Stuart,

As a user of 2 years (!) I trust that you know your way around iBATIS.
 It is very likely you've found a bug, and therefore I'd suggest you
put this in JIRA where we can track it and fix it.

I think you are probably right about the cause and if so, it should be
a simple solution.

Thanks for the accurate and detailed report!

Cheers,
Clinton


On Mon, 21 Feb 2005 22:32:13 +0000 (UTC), Stuart Piltch
<st...@gradientblue.com> wrote:
> Hi there,
> 
> 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 here 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). Can someone verify this? Should
> this type of message go in jira instead of here?
> 
> I'm using the latest build (2.0.9b.550)
> 
> Cheers,
> 
> - stuart
> 
> ps. I wanted to say thanks to Clinton and the development team! I've been using
> iBATIS for almost two years and it's great to see the community
> growing (not to mention the features of the framework itself).
> 
>