You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-cs@ibatis.apache.org by Martin Wickman <ma...@dapresy.com> on 2009/05/07 20:58:03 UTC
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
RE: Nested groupBy does not work
Posted by Martin Wickman <ma...@dapresy.com>.
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.wickman@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
RE: Nested groupBy does not work
Posted by Martin Wickman <ma...@dapresy.com>.
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.wickman@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