<load-collection> Need some help :/

maxime · **Joined:** Wed Mar 14, 2007 4:29 am **Posts:** 33

Hello,

i'm trying to load a collection with sql and i need some help.
I have 2 table:

Work
Coeff

Work can have a list of coeff but coeff only have one work.

Code:

So in the work mapping file i put:
<bag name="Coeffs" inverse="true" lazy="false">
      <key/>
      <one-to-many class="Rad.StaffPlan.Core.Domain.Coeff, Rad.StaffPlan.Core"/>
      <loader query-ref="LoadCoeffs"/>
</bag>

and at the end of the file:


  <sql-query name="LoadCoeffs">
    <load-collection alias="c" role="Rad.StaffPlan.Core.Domain.Work.Coeffs" />
    SELECT {c.*} FROM Coeff c WHERE c.IdWork = :id
  </sql-query>

i'm i doing wrong ? I can't use a simple <bag> because the query will be more complex after....

Thinks [/img]

maxime · **Joined:** Wed Mar 14, 2007 4:29 am **Posts:** 33

<bag name="Coeffs" inverse="true" table="Coeff" lazy="false">
<key column="idwork"/>
<one-to-many class="Rad.StaffPlan.Core.Domain.Coeff, Rad.StaffPlan.Core"/>
<loader query-ref="LoadCoeffs"/>
</bag>

works now !

maxime · **Joined:** Wed Mar 14, 2007 4:29 am **Posts:** 33

Another question:

how could i use various parameters (work parameters) ?

I use :IdWork and i would like to use another one. Is it in relation with the "key" declared in the bag ?

wolli · **Posted:** Wed Mar 05, 2008 4:57 pm

You can specifiy a filter on the collection. The filter can have as many parameters as you want

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/manipulatingdata.html#manipulatingdata-filtering

Afaik this filter works on the "loaded" collection, which means, that the items are filtered after fetching. If you want them to be filtered in the sql, you have to use this:

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/filters.html

maxime · **Joined:** Wed Mar 14, 2007 4:29 am **Posts:** 33

thanks, but i don't think i can do what i'm looking for with this solution.
In fact i would like to make a join with multiple parameters like this:

tables:

StaffPlan
-id
-idwork
-idversionCoeff

Work
-id

Coeff
-id
-idwork
-idmonth
-idyear
-version

what i want for my staffplan, is to load a collection of coeff but not only based on the idCoeff.

I need to load like this:

Code:

select c.* from coeff c, staffplan p 
where c.idwork = p.idword and c.version = 
     (select min(cc.version) 
     from coeff cc where 
     cc.idwork = c.idwork 
     and cc.idmonth = c.idmonth
     and cc.idyear c.idyear
     and cc.version >= p.Version)

So i have for example a filter on the p.Version which i don't think i can passed in parameter because it's the attribute of the current staffplan row....

Do you see what i mean ?

wolli · **Posted:** Thu Mar 06, 2008 5:41 am

You can specifiy a loader query in your collection mapping. Have a look:

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/querysql.html#querysql-load

maxime · **Joined:** Wed Mar 14, 2007 4:29 am **Posts:** 33

Yes, i have try to do something like this, it works when i only use the Id to join, but when i want tu use one other property i don't know how to do this:

Code:

<class name ="staffplan" ...

<property name="VersionCoeff"[ column="versionCoeff" type="int" not-null="true"/>

<bag name="Coeffs" inverse="true" table="Coeff"  lazy="false">
      <key>
        <column name="idWork"/>
      </key>      
      <one-to-many class="Rad.StaffPlan.Core.Domain.Coeff, Rad.StaffPlan.Core"/>
      <loader query-ref="LoadCoeffs"/>
    </bag> 
  <sql-query name="LoadCoeffs">

</class>

<load-collection alias="c" role="Rad.StaffPlan.Core.Domain.StaffPlan.Coeffs" />
    SELECT {c.*} FROM Coeff c, Year y, Month m
    WHERE y.ID = c.IdYear and y.IdMonth = c.IdMonth
    and c.IdWork = :IdWork
    and c.Version = 
      (SELECT min(cc.Version) 
      FROM Coeff cc WHERE cc.IdYear and cc.IdMonth = y.IdMonth 
      and cc.IdWork = :IdWork
      and cc.Version >= :VersionCoeff)
</sql-query-->

How can i tell him that :VersionCoeff correspond to the staffplan attribute of the current row ? (like the :IdWork)

wolli · **Posted:** Thu Mar 06, 2008 6:26 am

Since your collection is the inverse end, you probably have a StaffPlan property on Coeefs. Have you tried to add cc.StaffPlan.VersionCoeff instead of :VersionCoeff ?

maxime · **Joined:** Wed Mar 14, 2007 4:29 am **Posts:** 33

well it's a mistake i shouldn't have put inverse="true"...

It should be possible to know the attributes of the current row :/

Do you know how to get the VersionCoeff like this ?
I have tried to add <column name="VersionCoeff"/> but i launch an exception...