-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: left join fetch question
PostPosted: Tue Oct 12, 2004 12:33 pm 
Hi All,

I have a question about a left fetch join sql. My HQL is following:

"select e from Event e, Team t left join fetch e.markets " +
"where e.eventStatus='Open' and e.ultimateGroup.groupId = " + " t.ultimateGroup.groupId and t.teamId = ? and t.teamStatus = ?"

Here I would like to fetch list of Event with a Set of Market initialized. The join condition is Team's UltimateGroupID = Event's UltimateGroupID for given TeamId and TeamStatus. I have read the Action book and I could not find an example for deal of this situation. If given condition is from Event itself, that would be easy.

Any help is appreciated.

Mark


Top
  
 
 Post subject:
PostPosted: Tue Oct 12, 2004 12:44 pm 
Sorry, I did not include the error. The error is:

org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [null] in task 'HibernateAccessor';
nested exception is java.sql.SQLException: ORA-00904: "EVENT0_"."EVENT_ID": invalid identifier

java.sql.SQLException: ORA-00904: "EVENT0_"."EVENT_ID": invalid identifier

The Hibernate sql is:

Hibernate: select event0_.EVENT_ID as EVENT_ID0_, markets2_.MARKET_ID as MARKET_ID1_,
event0_.EVENT_NAME as EVENT_NAME0_, event0_.EVENT_SHORT_NAME as EVENT_SH3_0_,
event0_.EVENT_STATUS as EVENT_ST4_0_, event0_.NUMBER_OF_GAMES as NUMBER_O5_0_,
event0_.CREATION_TIME as CREATION6_0_, event0_.LAST_UPDATE_TIME as LAST_UPD7_0_,
event0_.LAST_UPDATE_BY as LAST_UPD8_0_, event0_.LAST_UPDATE_REASON as LAST_UPD9_0_,
event0_.RECORD_STATUS as RECORD_10_0_, event0_.ULTIMATE_GROUP_ID as ULTIMAT11_0_,
event0_.GROUP_ID as GROUP_ID0_, markets2_.GAME_NUMBER as GAME_NUM2_1_,
markets2_.MARKET_NAME as MARKET_N3_1_, markets2_.MARKET_SHORT_NAME as MARKET_S4_1_,
markets2_.MARKET_DATE as MARKET_D5_1_, markets2_.MARKET_STATUS as MARKET_S6_1_,
markets2_.FACE_VALUE_PRICE as FACE_VAL7_1_, markets2_.MAX_QUANTITY as MAX_QUAN8_1_,
markets2_.IS_FEATURED as IS_FEATU9_1_, markets2_.CREATION_TIME as CREATIO10_1_,
markets2_.LAST_UPDATE_TIME as LAST_UP11_1_, markets2_.LAST_UPDATE_BY as LAST_UP12_1_,
markets2_.LAST_UPDATE_REASON as LAST_UP13_1_, markets2_.RECORD_STATUS as RECORD_14_1_,
markets2_.EVENT_ID as EVENT_ID1_, markets2_.VENUE_ID as VENUE_ID1_,
markets2_.GROUP_ID as GROUP_ID1_, markets2_.ULTIMATE_GROUP_ID as ULTIMAT18_1_,
markets2_.PROGRAM_ID as PROGRAM_ID1_, markets2_.HOME_TEAM_ID as HOME_TE20_1_,
markets2_.AWAY_TEAM_ID as AWAY_TE21_1_, markets2_.MARKET_ID as MARKET_ID__,
markets2_.EVENT_ID as EVENT_ID__ from EVENT_TABLE event0_, TEAM_TABLE team1_
left outer join MARKET_TABLE markets2_ on event0_.EVENT_ID=markets2_.EVENT_ID where
(event0_.EVENT_STATUS='Open' )and(event0_.ULTIMATE_GROUP_ID=team1_.ULTIMATE_GROUP_ID )and
(team1_.TEAM_ID=? )and((team1_.TEAM_STATUS=? ))


Top
  
 
 Post subject:
PostPosted: Tue Oct 12, 2004 12:58 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
You would need to map the association between Team -> UltimateGroup -> Event (hibernate does not allow arbitrary or ad-hoc joins).

Then, the query would look something like:
Code:
SELECT e FROM Event AS e
    INNER JOIN FETCH e.markets AS m
    INNER JOIN e.ultimateGroup AS g
    INNER JOIN g.team AS t
WHERE e.eventStatus = :openStatus
    AND t.teamId = :myTeamId
    AND t.teamStatus = :myTeamStatus


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.