Query collection

Erik Forsberg · **Joined:** Wed Sep 19, 2007 2:59 am **Posts:** 14

Hi
Im woundering if this is possible in a simple HQL. Say i have the class Teacher, that has a bag of Courses, a Course can have a start date (StartDate).

I want to query and get all Teacher objects (with all their courses in the bag Courses) with a course that have a StartDate that is one month from now. Is this possible?

Thanks

Erik Forsberg · **Joined:** Wed Sep 19, 2007 2:59 am **Posts:** 14

No one knows?

wolli · **Posted:** Wed Jun 04, 2008 3:57 am

Try something like this. Maybe you can correlate the subquery with the corresponding teacher (depends on your Course class):

Code:

select t from Teacher t
  join fetch t.Courses
  where (from Course c where c.StartDate > :startDate) in elements(t.Courses)

Erik Forsberg · **Joined:** Wed Sep 19, 2007 2:59 am **Posts:** 14

Thanks, what do you meen? My Course have a property Teacher, its a bi-directional relationship.

With your query i get the error:

Code:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I rewrote it to, but i dont know if that was what you ment

Code:

select t from Teacher t 
  join fetch t.Courses 
  where (from Course c where c.StartDate > :startDate and c exists in elements(t.Courses))

And get the error

Code:

path expression ends in a composite value

A Course has a composite id, DateTime and a Teacher

It must be possible to do this kind of thing.

Thanks again

wolli · **Posted:** Wed Jun 04, 2008 4:50 am

Sorry, I didn't think enough ... my query will only work if the subquery returns a single value ... have you tried this:

Code:

select t from Teacher t
  join fetch t.Courses c
  where c.StartDate > :startDate

Normally, I use criteria queries for such things:

Code:

ICriteria crit = session.CreateCriteria(typeof(Teacher), "t")
   .CreateCriteria("Courses", "c")
   .Add(Expression.Gt("c.StartTime", startTime))
   .List<Teacher>();

Erik Forsberg · **Joined:** Wed Sep 19, 2007 2:59 am **Posts:** 14

Thanks, but using Criteria that way will generate one sql-question per Teacher i find. I really dont want that, will try som more hql. Thanks!

wolli · **Posted:** Wed Jun 04, 2008 5:04 am

Quote:

Thanks, but using Criteria that way will generate one sql-question per Teacher i find.

What do you mean with that ? If you're afraid of the selects for the courses, you can set the fetch mode on the criteria:

criteria.SetFetchMode("t.Courses", FetchMode.Join);

Erik Forsberg · **Joined:** Wed Sep 19, 2007 2:59 am **Posts:** 14

Im aware of that, but even if i use
criteria.SetFetchMode("t.Courses", FetchMode.Join);

It generates in several sql-querys when i do a profiler on sql-server, maybe it has to do with the double CreateCriteria?

wolli · **Posted:** Wed Jun 04, 2008 5:12 am

Didn't this work ?

Code:

select t from Teacher t
  join fetch t.Courses c
  where c.StartDate > :startDate

Erik Forsberg · **Joined:** Wed Sep 19, 2007 2:59 am **Posts:** 14

Thanks, i tried with the hql and it did work, but it resulted in one query for all the objects, and then for all the Courses for each Teacher. I really dont get why its beheaving like that

I really want it to only result in one sql-query, so im now trying to use ISQLQuery and AddEntity.

wolli · **Posted:** Wed Jun 04, 2008 5:29 am

Hibernate needs one query for finding the teachers which have courses with the specified startdate and a query for getting all courses for these teachers. If you want this in a single query, I think you need a subquery (which then isn't really a single query). Something like:

Code:

from Teacher t1
join fetch t1.Courses
where t1.Id in 
    (select id from Teacher t2 join t2.Courses c2 where c2.StartDate > :startDate)

Erik Forsberg · **Joined:** Wed Sep 19, 2007 2:59 am **Posts:** 14

Thanks ill try that