nested collection mapping - difference between 'id' and 'result' elements

[hlorofils]

I'm using mybatis 3.0.1, PostgreSQL 8.4 and have some trouble
understanding the difference between 'id' and 'result' elements. take
a look at this mapping:

<select id="getTestStories" resultMap="rmTestStory">
SELECT story_id, author, vote_type_id, vote_count FROM (
VALUES
(1, 'author1', 2, 1),
(1, 'author1', 3, 2),
(1, 'author1', 4, 4),
(2, 'author2', 1, 1),
(2, 'author2', 3, 2),
(2, 'author2', 4, 2)
) AS Votes (story_id, author, vote_type_id, vote_count)
ORDER BY 1 ASC
</select>

<resultMap id="rmTestStory" type="TestStory">
<id property="id" column="story_id"/>
<result property="author" column="author"/>
<collection property="voteCounts" ofType="VoteCount"
javaType="ArrayList">
<result property="voteTypeId" column="vote_type_id"/>
<result property="count" column="vote_count"/>
</collection>
</resultMap>

the important part is <result property="voteTypeId"
column="vote_type_id"/>. if it is set as an 'id' element, I get funny
results - for story with story_id = 2 and vote_type_id = 4 the
vote_count is 4 which is obviously wrong. on the other hand the
results are correct if I set the same mapping as 'result' element.

[Nathan Maves]

I believe that you can only have one id per resultMap.

[Sergii Volchkov]

Hello hlorofils,

I believe your real tables look something like:

create table stories (story_id int primary key, author varchar(...));
create table story_votes (story_id int, vote_type_id int, vote_count
int);

and the actual select query is a join on the tables.

In story_votes table, vote_type_id itself is not a unique key -
therefore, it cannot be used as <id> in myBatis result map.
You could try the following options:

* use a pair (story_id, vote_type_id) as a key (you will probably use
it as unique constraint on the story_votes table too);
* add an explicit primary key column to the story_votes;
* leave your result map as is, without the <id> mapping.

On the other hand, what I would like to know/see in the documentation
is a more detailed explanation of the impact of using <id> vs.
<result> on performance.

[hlorofils]

Yes, the actual query is a join - this is just a simplified example to
prove the point.

All of your suggested solutions will work, but I opted for the first
one because it seems more idiomatic. So the mapping now looks like
this:

<resultMap id="rmTestStory" type="TestStory">
<id property="id" column="story_id"/>
<result property="author" column="author"/>
<collection property="voteCounts" ofType="VoteCount"
javaType="ArrayList">

<id property="storyId" column="story_id"/>
<id property="voteTypeId" column="vote_type_id"/>

<result property="count" column="vote_count"/>
</collection>
</resultMap>

Thank you for your advice, Sergii!