Hi,

I have a query like

"from TimeSheet as timeSheet left join fetch timeSheet.timeEntry " +
"where timeSheet.employee.department.id=:id " +
"and timeSheet.employee.approver.approverLevel.level < :approval " +
"and timeSheet.date = :date " +
"order by timeSheet.employee.id asc"

But this will return an entry for every time entry, even though I just want to initialize the timeEntry collection in timeSheet.

Is there either a way to make the timeSheets distinct (since that is all I'm really selecting) or make so that the timeEntry's are not returned?

I've tried "select distinct timeSheet from TimeSheet ..." but the order by complains. I've also tried "from timeSheet ... group by timeSheet" but then it complains that timeSheet.id is not in the select.

Please advise.

Take a look at this thread:

http://forum.hibernate.org/viewtopic.php?t=926689

Joe

Hi,

Thanks for the reply. That is already what i'm doing currently using "new HashSet(result)". However, that seems terribly inefficient.
Would it be more efficient to just get the collection (without the join fetch) and then loop through it to initialize the collection within each timeSheet? If so, what would be an efficient way to initialize them?

If you only expect a few TimeSheets to be returned, you may not see much of a difference. The more TimeSheets returned, the more selects would need to be executed. I think I would prefer more data returned in a single select than to hit the database many times.

Joe

Very true.. thanks for the advice.

Even though there won't be that many timesheets at once, there will be many time entries.

I am unclear on how to even initialize a collection. I tried to call the iterator for the Collection but I don't think it worked (I then stored the timeSheet in the session and then tried to access the collection later).

another problem I am having with using new HashSet(result) is that it ruins the "order by" sort that I imposed in the query.

You could use a SortedSet to do the ordering instead.

that is a good idea, but even then, using the query api my query results limit will be useless since it will return an variable number of timeentries per timesheet.

any suggestions for that one? =]