Merging 2 one-to-many results

tdelesio · **Joined:** Thu Apr 24, 2008 7:43 pm **Posts:** 6

Hi,

I'm fairly new to hibernate and am trying to solve a rather complex problem.
I have 3 classes that I have relationships on...Player, Picks, and Weeks

Code:

<class name="Player" table="profile">
<set  name="picks" table="picks" cascade="save-update" lazy="false" inverse="true" >
            <key column="username_id"/>
            <one-to-many class="Picks"/>
       </set>
</class>

 <class name="Picks" table="picks">
<many-to-one name="week" column="week_id" lazy="false" fetch="select" cascade="none" class="Week"/>
<many-to-one name="name" column="username_id" lazy="false" fetch="select" cascade="none" class="Player"/>
</class>

<class name="Week" table="weeks">
 <set name="picks" inverse="true" lazy="false" fetch="select" table="picks">
            <key column="week_id"/>
            <one-to-many class="Picks"/>
        </set>
</class>

Code:

public class Player extends AbstractPersistantObject implements UserDetails{
private Set picks=new HashSet();
}

public class Picks extends AbstractPersistantObject{
   private Player name;
   private Week week;
}

public class Week extends AbstractPersistantObject implements Serializable{
   private Set picks = new HashSet();
}

When I call player.getPicks I get all the picks for the player. When I call week.getPicks I get all the picks for the Week. What I really want is a way to get all the picks for the player for a given week. I could do it in the code by iterating through each set and picking and choosing what I wanted but I wanted to know if there was a way to do this in hibernate? Any suggestions?

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

It looks as something you can easily achieve making a SQL inner join between picks, weeks and profiles, with parameters for the week and the player.

tdelesio · **Joined:** Thu Apr 24, 2008 7:43 pm **Posts:** 6

One of my friends suggested that I use a hibernate filter to handle this. Are there any examples of this? The one in the hibernate documentation does not include how to handle it on the Class side.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

This is the simple SQL query.

Code:

select
  pr.*,
  we.*
from profile pr inner join picks pi on pi.username_id=pi.username_id
                      inner join weeks we on pi.week_id=we.week_id
where 
       pr.username_id=?
       and we.week_id=?

What hibernate technique you use to obtain it
(HQL, a criteria query, direct SQL) is not so relevant.