How to use <load-collection>?

sen.chi.jin · **Joined:** Thu Nov 01, 2007 4:17 pm **Posts:** 3

Hi people,

I need a help for use load-collection. I m tring to create the map file as follow the documentation but don´t working.

My Map file:

Code:

<class name="Grupo" table="grupo">
        <id name="Codigo" column="gru_id_grupo" type="Int32" unsaved-value="0"> 
            <generator class="identity" /> 
        </id> 
        <property name="Nome" column="gru_no_grupo" type="String" not-null="true" unique="true" length="30"/>
       <bag name="Membros" table="usu_grupo" lazy="false" cascade="none">
          <key column="gru_id_grupo" />
          <many-to-many column="usu_id_usuario" class="Usuario" />
           <loader query-ref="SQL.Query.Membros"/>
       </bag>
</class>
<sql-query name="SQL.Query.Membros">
       <load-collection alias="USU" role="Grupo.Membros"/>
         SELECT 
            {USU.*}
         FROM 
            usu_grupo RIGHT OUTER JOIN Usuario USU ON ( Usu_grupo.usu_id_usuario = Usuario.usu_id_usuario )
         WHERE 
            gru_id_grupo = ?
         Order By 
            usu_no_usuario
</sql-query>

what is wrong? I m get the error message: "Could not initialize collection"
The sql query is correct and result 4 rows

Sorry for my english :S

Thanks!

stovesy · **Joined:** Tue Jun 06, 2006 4:47 am **Posts:** 25

I'm having a related problem:
2 Scenarios

Scenario 1 :

Code:

   <!-- A list of roles which can be assigned to a user -->
   <set name="AvailableRoles" lazy="true">
      <key/>
      <one-to-many class="Role"/>
      <loader query-ref="SQLGetAvailableRoles"/>
   </set>

   <sql-query name="SQLGetAvailableRoles">
      <load-collection alias="" role="User.AvailableRoles">
      </load-collection>
      SELECT * FROM roles r WHERE r.id NOT IN
      (SELECT rolepointer FROM userroles WHERE userpointer=:ID)
   </sql-query>

This actually executes, the log files say all entities are populated, but the collection remains empty.

After a little browsing around, I found the following: http://wiki.nhibernate.org/browse/NH-794 which seems to give the answer.

So after a few changes, we get to

Scenario 2 :

Code:

   <sql-query name="SQLGetAvailableRoles">
      <load-collection alias="" role="User.AvailableRoles">
         <return-property name="key" column="id"/>
      </load-collection>
      SELECT * FROM roles r WHERE r.id NOT IN
      (SELECT rolepointer FROM userroles WHERE userpointer=:ID)
   </sql-query>

As soon as I add in the <return-property> elements the whole thing is failing with - "Column index out of range".
The problem occurs on Loader.cs at GetKeyFromResultSet, where it seems nHibernate is looking for (in my case) a field named id2_0_ where the result set contains the plain field names from the table.

So, I'm guessing I'm doing something wrong when it comes to specifying an alias for field or something.

Will keep chipping away at it.
Hope someone can help.

stovesy · **Joined:** Tue Jun 06, 2006 4:47 am **Posts:** 25

Sorted

Scenario 2 does indeed work
You have to be very careful and provide a <return-property> element for each property in your object, and for the collection key, which I understand as being the primary key identifier of the object owning the collection (the parent):

Code:

   <return-property name="key" column="collectionkey"/>

   <return-property name="element" column="id"/>
   <return-property name="element.ID" column="id"/>
   <return-property name="element.Application" column="application"/>
   <return-property name="element.Name" column="rolename"/>
   <return-property name="element.Description" column="description"/>
   <return-property name="element.ReadOnly" column="readonly"/>

The important bit of the query is returning the parent key ID (in the cast)

Code:

   SELECT CAST(:ID AS integer) AS collectionkey, * FROM roles r WHERE r.id NOT IN
      (SELECT rolepointer FROM userroles WHERE userpointer=:ID)

It seems you cannot use the {USU.*} type of notation in your query.

Hope this helps.