Anyone?
To recap: it seems that multiple groupBy's in iBatis is done without any
nesting context. Is this correct? What is the general approach to how to handle
these cases?
-----Original Message-----
From: Martin Wickman
Sent: den 8 maj 2009 09:16
To: 'user-cs@ibatis.apache.org'
Subject: RE: Nested groupBy does not work
Hello
I discovered one bad workaround which is to use two grouping fields. In this
case adding 'groupBy="batchID, id"' to the second resultMap AND adding a new
batchID property to the Attribute class. See below.
<resultMaps>
<resultMap id="batchesResult" class="Batch" groupBy="id">
<result property="id" column="BatchID" />
<result property="attributes" resultMapping="Batch.a1" />
</resultMap>
<resultMap id="a1" class="Attribute" groupBy="batchID, id" > <!-- added
batchID here -->
<result property="id" column="AttributeID"/>
<result property="batchID" column="BatchID" /> <!-- added
property -->
<result property="values" resultMapping="Batch.a2"/>
</resultMap>
<resultMap id="a2" class="AttributeValue">
<result property="id" column="AttributeValueID"/>
</resultMap>
</resultMaps>
Obviously this is not elegant and, even though it solves this case, makes it
impossible to reuse result maps which is one major reason to use iBatis.
Any takes on this please?
Cheers
/Martin
-----Original Message-----
From: Martin Wickman [mailto:martin.wick...@dapresy.com]
Sent: den 7 maj 2009 20:58
To: user-cs@ibatis.apache.org
Subject: Nested groupBy does not work
Hello
I have, desperately, been trying to get a join with two nested groupBy's to
work in iBatis.NET (1.6.1 and 1.6.2) to work, but alas no :-(
The SQL "getBatches" (see sqlmap below) yields a table which looks exactly like
this:
BatchID AttributeID AttributeValueID
29 1 2
29 2 3
29 2 4
29 2 5
30 2 3
30 2 4
30 2 5
The structure is hierarchical: A Batch contains Attribute's which contains
AttributeValue's. That is, the objects should be like this once completed:
batch[0].attributes[0].values[0].id == 2
batch[0].attributes[1].values[0].id == 3
batch[0].attributes[1].values[1].id == 4
batch[0].attributes[1].values[2].id == 5
batch[1].attributes[0].values[0].id == 3
batch[1].attributes[0].values[1].id == 4
batch[1].attributes[0].values[2].id == 5
Here is my sqlmap:
<statements>
<select id="getBatches" resultMap="batchesResult" >
... generates the above table ...
</select>
</statements>
<resultMaps>
<resultMap id="batchesResult" class="Batch" groupBy="id">
<result property="id" column="BatchID" />
<result property="attributes" resultMapping="Batch.a1" />
</resultMap>
<resultMap id="a1" class="Attribute" groupBy="id" >
<result property="id" column="AttributeID"/>
<result property="values" resultMapping="Batch.a2"/>
</resultMap>
<resultMap id="a2" class="AttributeValue">
<result property="id" column="AttributeValueID"/>
</resultMap>
</resultMaps>
Running the above with:
IList<Batch> batches = mapper().QueryForList("getBatches", null);
Gives the following object structure
Batch.id 29
Attribute.id 1
Value.id 2
Attribute.id 2
Value.id 3 |
Value.id 4 |
Value.id 5 |
Value.id 3 | Note the repetition here.
Value.id 4 | These three (3,4,5) should belong to
Value.id 5 | batch id 30
Batch.id 30
(Zero attributes)
But it *should* look like this:
Batch.id 29
Attribute.id 1
Value.id 2
Attribute.id 2
Value.id 3
Value.id 4
Value.id 5
Batch.id 30
Attribute.id 2
Value.id 3
Value.id 4
Value.id 5
Apparently the groupBy is not working. Values with id 2 from Batch 30 is added
to Batch 29. From my tests, the general conclusion is that all Attributes with
the same id's ends up in the first Batch. Attributes that are "unique" for a
certain Batch, is handled correctly.
This suggests to me that grouping is done without any nesting context. It
should work like "GROUP BY BatchID, AttributeID " imo.
Googling around for examples points to things like
http://article.gmane.org/gmane.comp.java.ibatisdb.user/383
https://issues.apache.org/jira/browse/IBATIS-79
https://issues.apache.org/jira/browse/IBATIS-260
Which seems to tell that this issue is fixed or related to my problems.
I may be doing something terribly wrong and silly, but is very much stuck at
this point. So any help would be most appreciated!
/Thanks
Martin
