1

I am trying to return a List of Objects with the following HQL-Statement:

return getHibernateTemplate().findByNamedParam("Select distinct response.user
    from Survey survey
    inner join fetch survey.surveyDates surveyDate
    inner join fetch surveyDate.responses response
    where survey.id = :surveyId)", "surveyId", surveyId);

The following error drives me mad:

HTTP Status 500 - query specified join fetching, but the owner of the fetched association was not present in the select list

So I tried it without the word "fetch" in my code. Then my returning list is empty.

The Application is like Doodle, the Database looks like this:

Survey has a list surveyDates
SurveyDate has a list of responses
Response has an attribute user

My task was: The method has a parameter surveyId and returns all users, that responded to it. So we have to go down all the way Survey --> surveyDates --> responses --> user

I guess thats pretty easy for you, i am new to SQL and even newer to Hibernate!

rolve
  • 10,083
  • 4
  • 55
  • 75
superHH
  • 239
  • 3
  • 9

1 Answers1

0

Your query seems incorrect : if you are expecting for a users list, you should start your from clause with user (but as you did not provide any mapping, I am just expecting that you made an inverse relationship between user and reponse and so on)

It should be better with something like this :

from user u
inner join u.responses as response 
inner join response.surveydates as surveydates
inner join surveydates.survey as survey
where survey.id = :surveyId
willome
  • 3,062
  • 19
  • 32
  • Unfortunately there is no inverse relationship..So this won't work. Is it still possible? – superHH Nov 08 '12 at 22:02
  • It depends from your object model (not your database) : does User hold a reference to Response in your object model? . If you do not have a reference to the Response from a User, then there is not a single way you will get just a List of users. Because a User will never know what its response's ID is. – willome Nov 08 '12 at 22:38
  • Thank you for your input. Actually, the User does not hold any references. Its a one way street. Only Response is holding a reference to the user... Looks like that was not the best idea, right? – superHH Nov 08 '12 at 23:04
  • Ouh actually what i was able to do: On the Database itself (H2, if that matters) i was able to execute a SQL Statement that returned exactly what i wanted. But therefore i needed the jointables. Here it is: SELECT distinct res.user_id FROM SURVEY sur, SURVEY_SURVEYDATE sur_surdate, SURVEYDATE_RESPONSE sur_res, RESPONSE res WHERE sur.id = 1 and sur.id = sur_surdate.survey_id and sur_surdate.surveydates_ID = sur_res.surveydate_id and sur_res.responses_id = res.id (sur.Id = 1 just to test instead of parameter) But there is no way to do thatin hibernate? – superHH Nov 08 '12 at 23:05
  • I do not see any join on User in your query, so this is not exactly what you are looking for. – willome Nov 08 '12 at 23:37
  • Adding a bidirectional relationship or not is just your choice : try to ask yourself the good questions according to your application (What entities should I want to show/insert/update). In your case User must have a reference to Response – willome Nov 08 '12 at 23:42