When can I omit 'column' attribute in associations and collections?

[Antonio Sánchez]

Hi all.

I see in the user guide that 'column' it is not always used in the "Very
Complex Result Map" example.

http://www.mybatis.org/core/sqlmap-xml.html#Result_Maps (Advanced Result
Maps)

<collection property="posts" ofType="Post">

<collection property="comments" column="post_id" ofType=" Comment">

So, when can I omit 'column' in both associations and collections?

Thanks.

--
Antonio.

[Antonio Sánchez]

No one to shed some light?

It seems that if column is omitted then MyBatis infers some value.

Please help, I would like to contribute documentation on this subject,
which I think is the top key in nested mapping.

This is the full result map:

<resultMap id="detailedBlogResultMap" type="Blog">
  <constructor>
    <idArg column="blog_id" javaType="int"/>
  </constructor>
  <result property="title" column="blog_title"/>
  <association property="author" column="blog_author_id" javaType="
Author">
    <id property="id" column="author_id"/>
    <result property="username" column="author_username"/>
    <result property="password" column="author_password"/>
    <result property="email" column="author_email"/>
    <result property="bio" column="author_bio"/>
    <result property="favouriteSection"
column="author_favourite_section"/>
  </association>
  <collection property="posts" ofType="Post">
    <id property="id" column="post_id"/>
    <result property="subject" column="post_subject"/>
    <association property="author" column="post_author_id"
javaType="Author"/>
    <collection property="comments" column="post_id" ofType="
Comment">
      <id property="id" column="comment_id"/>
    </collection>
    <collection property="tags" column="post_id" ofType=" Tag" >
      <id property="id" column="tag_id"/>
    </collection>
    <discriminator javaType="int" column="draft">
      <case value="1" resultType="DraftPost"/>
    </discriminator>
  </collection>
</resultMap>

On 3 feb, 12:45, Antonio Sánchez <[hidden email]> wrote:

> Hi all.
>
> I see in the user guide that 'column' it is not always used in the "Very
> Complex Result Map" example.
>
> http://www.mybatis.org/core/sqlmap-xml.html#Result_Maps(Advanced Result
> Maps)
>
> <collection property="posts" ofType="Post">
>
> <collection property="comments" column="post_id" ofType=" Comment">
>
> So, when can I omit 'column' in both associations and collections?
>
> Thanks.
>
> --
> Antonio.

[Eduardo Macarron]

AFAIK, column attribute is mandatory with nested selects.

[Eduardo Macarron]

Note the difference between nested selects and nested resultmaps. On a
nested select you usually need a value to build the where clause,
hence a column is needed and MyBatis will get the value from the
resultset asking for that column.

Nested resultmaps work different. All data is already available (as a
result of a join) so no column is needed.

On 8 feb, 14:45, Eduardo <[hidden email]> wrote:
> AFAIK, column attribute is mandatory with nested selects.

[Antonio Sánchez]

In the example of user guide posted above we only have nested
resultmaps and no nested selects. The first collection there, but not
the rest of nested resultmaps, is omitting the 'column' attribute.

Then the question is:

1. When can I omit the 'column' attribute in nested resultmaps?
2. How does mybatis infer a 'foreign key' if 'column' is omitted?

Somehow what I am asking is a precise definition of the 'column'
attribute, in nested resultmaps and nested selects.

El mié 08 feb 2012 14:58:28 CET, Eduardo escribió:
> Note the difference between nested selects and nested resultmaps. On a
> nested select you usually need a value to build the where clause,
> hence a column is needed and MyBatis will get the value from the
> resultset asking for that column.
>
> Nested resultmaps work different. All data is already available (as a
> result of a join) so no column is needed.

[Eduardo Macarron]

I see. I would say that is wrong in doc.

> 1. When can I omit the 'column' attribute in nested resultmaps?
You don't need it...  Look. You do this select

select author_name, post_content from author a join post p on
a.author_id = p.author_id

Look that the foreing key is p.author_id but it is not retrieved and
not needed.

MyBatis will read the result set line by line. On each line it will
create an Author (if needed), a Post and will add the Post to
List<Post> posts Author's property.

But it you are going to execute a nested select you need the FK
select autor_id, author_name from author
select post_content from post where post_id = #{id}

> 2. How does mybatis infer a 'foreign key' if 'column' is omitted?

It can't. That is why column is mandatory in nested selects :)

[Eduardo Macarron]

Sorry I meant:

select autor_id, author_name from author
select post_content from post where author_id = #{id}

On 8 feb, 18:34, Eduardo <[hidden email]> wrote:

> I see. I would say that is wrong in doc.
>
> > 1. When can I omit the 'column' attribute in nested resultmaps?
>
> You don't need it...  Look. You do this select
>
> select author_name, post_content from author a join post p on
> a.author_id = p.author_id
>
> Look that the foreing key is p.author_id but it is not retrieved and
> not needed.
>
> MyBatis will read the result set line by line. On each line it will
> create an Author (if needed), a Post and will add the Post to
> List<Post> posts Author's property.
>
> But it you are going to execute a nested select you need the FK
> select autor_id, author_name from author
> select post_content from post where post_id = #{id}
>
> > 2. How does mybatis infer a 'foreign key' if 'column' is omitted?
>
> It can't. That is why column is mandatory in nested selects :)

[Eduardo Macarron]

BTW seems that the doc is based on this test:

src/test/java/org/apache/ibatis/builder/BlogMapper.xml

It has colums on nested resultmaps but works perfectly without them :)

On 8 feb, 18:36, Eduardo <[hidden email]> wrote:

> Sorry I meant:
>
> select autor_id, author_name from author
> select post_content from post where author_id = #{id}
>
> On 8 feb, 18:34, Eduardo <[hidden email]> wrote:
>
>
>
>
>
>
>
> > I see. I would say that is wrong in doc.
>
> > > 1. When can I omit the 'column' attribute in nested resultmaps?
>
> > You don't need it...  Look. You do this select
>
> > select author_name, post_content from author a join post p on
> > a.author_id = p.author_id
>
> > Look that the foreing key is p.author_id but it is not retrieved and
> > not needed.
>
> > MyBatis will read the result set line by line. On each line it will
> > create an Author (if needed), a Post and will add the Post to
> > List<Post> posts Author's property.
>
> > But it you are going to execute a nested select you need the FK
> > select autor_id, author_name from author
> > select post_content from post where post_id = #{id}
>
> > > 2. How does mybatis infer a 'foreign key' if 'column' is omitted?
>
> > It can't. That is why column is mandatory in nested selects :)

[Eduardo Macarron]

I have updated the doc. Could you give it a review?
http://www.mybatis.org/core/sqlmap-xml.html#Result_Maps

Thanks once again for your help Antonio!

On 8 feb, 18:47, Eduardo <[hidden email]> wrote:

> BTW seems that the doc is based on this test:
>
> src/test/java/org/apache/ibatis/builder/BlogMapper.xml
>
> It has colums on nested resultmaps but works perfectly without them :)
>
> On 8 feb, 18:36, Eduardo <[hidden email]> wrote:
>
>
>
>
>
>
>
> > Sorry I meant:
>
> > select autor_id, author_name from author
> > select post_content from post where author_id = #{id}
>
> > On 8 feb, 18:34, Eduardo <[hidden email]> wrote:
>
> > > I see. I would say that is wrong in doc.
>
> > > > 1. When can I omit the 'column' attribute in nested resultmaps?
>
> > > You don't need it...  Look. You do this select
>
> > > select author_name, post_content from author a join post p on
> > > a.author_id = p.author_id
>
> > > Look that the foreing key is p.author_id but it is not retrieved and
> > > not needed.
>
> > > MyBatis will read the result set line by line. On each line it will
> > > create an Author (if needed), a Post and will add the Post to
> > > List<Post> posts Author's property.
>
> > > But it you are going to execute a nested select you need the FK
> > > select autor_id, author_name from author
> > > select post_content from post where post_id = #{id}
>
> > > > 2. How does mybatis infer a 'foreign key' if 'column' is omitted?
>
> > > It can't. That is why column is mandatory in nested selects :)

[Antonio Sánchez]

No my friend, thanks to you!

Give me some time check it and I will let you.

El mié 08 feb 2012 19:14:47 CET, Eduardo escribió:
> I have updated the doc. Could you give it a review?
> http://www.mybatis.org/core/sqlmap-xml.html#Result_Maps
>
> Thanks once again for your help Antonio!

[Antonio Sánchez]

Some comments and thoughts.

1. About documentation:

I see you have: removed 'column' in big result map example; removed
'column' in attributes table for association; add column in nested
association attributes table. Am I missing anything else?

I would:

a) Remove column in first example at the beginning of 'association' section.
b) Replace " the column to retrieve the value from" in the first
paragraph in 'association' section and following the first example, and
clearly state that the column attribute must be used in nested selects
and ignored in nested resultmaps.
c) Remark in "Column" row in table for "Nested Select for Association"
that this attribute acts like the foreign key, like you stated earlier.
d) Personally the sentence "This is the same string that would normally
be passed to resultSet.getString(columnName)" looks confusing for me.
e) Clearly and explicitly warn about this issue somewhere. For example:
"Tip/Note/Important: Use 'column' attribute only for nested selects, it
is mandatory there, but do NOT use it in nested resultmaps because it is
not necessary and it will be ignored by MyBatis engine."
f) Add some section in the future (an appendix, for example) that deeps
into this and other points that would help the newbie mybatis developer
to understand the usage of advanced resultmaps.
g) Document this issue in code. I guess the right place to comment is
the DTD, but maybe some other parts in javadoc too.

I CAN HELP YOU WITH DOCUMENTATION IF YOU WISH, just let me know and I
will send a diff patch.

2. In previous 1.e) comment I am assuming, according to some testing I
have performed, that MyBatis completely ignores 'column' in nested
resultmaps, even it an unknown column name is provided. This holds true
for collections, but I'm not sure in the case of associations where a
wrong column name may even make mybatis fail at startup, but as I say
I'm not sure of this.

3. Is there any way in DTD to force that 'column' MUST be specified in
nested selects but MUST NOT be specified in nested resultmaps? I don't
think DTD is as expressive as this but I don't have DTD fresh in my mind
and that's why I'm asking about it.

4. I have done some testing with the example you have proposed:

<select id="getAutoresConArtículos" resultMap="NestedRM"">
         select author_name,
             post_content
         from author a
             join post p on a.author_id = p.author_id
</select>

<resultMap id="NestedRM" type="Author">
<result property="name" column="author_name" />
<collection property="posts" ofType="Post" >
<result property="content" column="post_content"/>
</collection>
</resultMap>

And it works perfectly, which is amazing for me because it is not using
ids (primary keys). What would happen if we have different authors with
same name?

I guess all stated above holds using ids, which besides is preferred:

<select id="getAutoresConArtículos" resultMap="NestedRM"">
         select author_name,
             post_content,
             a.id AS author_id,
             p.id AS post_id
         from author a
             join post p on a.author_id = p.author_id
</select>

<resultMap id="NestedRM" type="Author">
<id property="id" column="author_id" />
<result property="name" column="author_name" />
<collection property="posts" ofType="Post" >
<id property="id" column="post_id"/>
<result property="content" column="post_content"/>
</collection>
</resultMap>

Better?

5. In this issue:

a) Does it anything to do the fact that the database tables involved
keep referential integrity?
b) Does it anything to do the fact that the query is outer or inner join?

--------

I hope this helps future mybatis users to quickly understand the details
of advanced result mapping. Personally I have found it very difficult to
understand this part because of the confusion around 'column' attribute.

Kind regards!


El mié 08 feb 2012 19:14:47 CET, Eduardo escribió:
>> I have updated the doc. Could you give it a review?
>> http://www.mybatis.org/core/sqlmap-xml.html#Result_Maps
>>
>> Thanks once again for your help Antonio!

[Antonio Sánchez]

Patch issued:

http://code.google.com/p/mybatis/issues/detail?id=522

Please check. It could be useful for new users.

[Eduardo Macarron]

Thank you Antonio. I will have a loot at it this weekend.

On 17 feb, 20:32, Antonio Sánchez <[hidden email]> wrote:
> Patch issued:
>
> http://code.google.com/p/mybatis/issues/detail?id=522
>
> Please check. It could be useful for new users.