Hello
My associations are listed below in the mapping file snippets.
I want to retrieve all games and if exists the tip for the game of a selected user. If the user hasn't made a tip for a game, null should be returned.
Among other queries I tried the following:
Code:
from Game g left outer join g.tips t where t.user = user and user = :user
The problem with the mentioned query is, that the where clause is applied to the whole query, not only to the tips collection. I got just the games back that the user has already tipped for instead of all available games.
My SQL query would look like the following:
Code:
select * from game g left join (select * from tip t where t.user_id = 'USER_ID') as user_tips on g.id = user_tips.game_id
How you can see it's just a left outer join on a subselect.
Am I able to achieve what I want with Hibernate? If so does anyone has a hint for me?
Thanks
Cheers Martin
Hibernate version: 3.1.3
Mapping documents:tip:
Code:
<many-to-one name="user" class="ch.erzberg.strafraum.User" column="user_id" not-null="true"/>
<many-to-one name="game" lazy="false" class="ch.erzberg.strafraum.Game" column="game_id" not-null="true"/>
game:
Code:
<!-- collection of tips -->
<set name="tips" lazy="true" inverse="true" cascade="all-delete-orphan">
<key column="game_id"/>
<one-to-many class="ch.erzberg.strafraum.Tip"/>
</set>
user:
Code:
<!-- collection of tips -->
<set name="tips" lazy="true" inverse="true" cascade="all-delete-orphan">
<key column="user_id"/>
<one-to-many class="ch.erzberg.strafraum.Tip"/>
</set>
Code between sessionFactory.openSession() and session.close():
Full stack trace of any exception that occurs:
Name and version of the database you are using:
Postgresql 8.1
The generated SQL (show_sql=true):
Debug level Hibernate log excerpt:
[list=][/list]
Login
FAQ
Search
