I've using different o/r mappers for some time now and settled on NHibernate since this has alot of the features I want. I however have some difficulty getting the things I want when using hql.
All the "normal" stuff with simple where and order by clauses are pretty easy, but whenever I want to do some more "advanced" queries with fx prefetch paths I can't really get my head around it.
Just to give you a quick example of a problem I have right now is the following scenario:
A Course can have a number of Enrollments. I want to display a list of Courses where you in the list can see how many users are Enrolled to the Course. Therefor I don't want to lazy load the enrollments collection of the course since I would get a db call for
each course.
My first attempt at this looks like this:
"from Course as c left join fetch c.Enrollments where name like 'asdas%' order by name"
But this worked much like a "normal" sql join and I would get have the same course returned multiple times (one for each enrollment).
And...
2) any good ideas on how this could be accomplished (maybe without hql, but with Expressions )
typeof( Course ) ).Add( Expression.Like( "Name", "yada yada%" ) ).SetFetchMode( "Enrollments", FetchMode.Eager ).AddOrder( Order.Desc( "Name" ) ).List();
With the excact same result. Bummer... I did however find a partial hack....
ISet courses2 =
new HashedSet( courses );
While the duplicate objects are still retrieved from the database, the HashedSet eliminates the dublicate ones. Not the best solution but probably the one I will use.
Sorry that I answer myself, but if anybody has a nicer solution to this I would be more than happy to hear from you...
You can handle this just like you would with sql. You could A) use a distinct. or B) use an Exists clause which would then also have a left join fetch to the Enrollments in order to retrieve everything in one single query. Basically select all courses left
join fetch enrollments where there exists an enrollment for me. Does that make sense?
JayC: I'm not sure that I can use the "distinct" keyword. The problem is that if I have 2 courses, one of them with 2 enrolled and the other with 3 enrolled, I get 5 rows returned from the query. This will again give me 5 course objects (even though there is
only 2). I might have misunderstood you, so could you provide an example on what you mean?
Mynameismud: I probably go with your example. Using the HashedSet was a quick way of doing it, but in order to do sorting I have to implement IComparable and this would be sorted outside NHibernate. Even though I'm not fully satisfied with the solution, it's
the best at the moment and there is not retrieve to much info from the database, there is just generated too many business objects from NHibernate. Like many other I have a Facade (I call it Broker) to control the sessions and stuf like that, so I'll probably
just make the "Flatten" method a part of that so it's transparent to the client.
Re-Read what I wrote. You can 100% use distinct. The only issue will be that you will make a second query to get the students enrolled if you want that. You just need to do "SELECT DISTINCT c FROM Course c INNER JOIN Enrollment e WHERE e.UserType = :student"
You will now retrieve only 2 courses in the example you just gave.
Alternatively you could do "SELECT c FROM Course c WHERE EXISTS (SELECT e FROM Enrollment e WHERE e.Course = c AND e.UserType = :student)".
Both of these will work. Try it. Note I just wrote these so there may be some syntax errors on your first try.
macrap
Participant
870 Points
174 Posts
NHibernate HQL
Oct 19, 2005 07:35 AM|LINK
I've using different o/r mappers for some time now and settled on NHibernate since this has alot of the features I want. I however have some difficulty getting the things I want when using hql.
All the "normal" stuff with simple where and order by clauses are pretty easy, but whenever I want to do some more "advanced" queries with fx prefetch paths I can't really get my head around it.
So...
1) Do you know of any good resources that are explaining the hql. My main resource at the moment is the Hibernate help: http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html
Just to give you a quick example of a problem I have right now is the following scenario:
A Course can have a number of Enrollments. I want to display a list of Courses where you in the list can see how many users are Enrolled to the Course. Therefor I don't want to lazy load the enrollments collection of the course since I would get a db call for each course.
My first attempt at this looks like this:
"from Course as c left join fetch c.Enrollments where name like 'asdas%' order by name"
But this worked much like a "normal" sql join and I would get have the same course returned multiple times (one for each enrollment).
And...
2) any good ideas on how this could be accomplished (maybe without hql, but with Expressions )
Thanx...
Sane Productions
- Because it's too easy being insane
www.sane.dk
macrap
Participant
870 Points
174 Posts
Re: NHibernate HQL
Oct 19, 2005 11:22 AM|LINK
Apparently it's not possible to do with a join:
http://jira.nhibernate.org/browse/NH-300
I just tried to do it with a criteria as well...
courses = session.CreateCriteria(
typeof( Course ) ).Add( Expression.Like( "Name", "yada yada%" ) ).SetFetchMode( "Enrollments", FetchMode.Eager ).AddOrder( Order.Desc( "Name" ) ).List(); With the excact same result. Bummer... I did however find a partial hack....ISet courses2 =
new HashedSet( courses ); While the duplicate objects are still retrieved from the database, the HashedSet eliminates the dublicate ones. Not the best solution but probably the one I will use.Sorry that I answer myself, but if anybody has a nicer solution to this I would be more than happy to hear from you...
Sane Productions
- Because it's too easy being insane
www.sane.dk
mynameismud
Participant
1210 Points
242 Posts
Re: NHibernate HQL
Oct 19, 2005 11:48 AM|LINK
{
ArrayList items = new ArrayList();
for (int i = 0; i < result.Count; i++)
{
if (items.Contains(result[i])) continue;
items.Add(result[i]);
}
return items;
}
Hope this helps
JayC202
Member
725 Points
145 Posts
Re: NHibernate HQL
Oct 19, 2005 02:57 PM|LINK
macrap
Participant
870 Points
174 Posts
Re: NHibernate HQL
Oct 20, 2005 07:28 AM|LINK
Mynameismud: I probably go with your example. Using the HashedSet was a quick way of doing it, but in order to do sorting I have to implement IComparable and this would be sorted outside NHibernate. Even though I'm not fully satisfied with the solution, it's the best at the moment and there is not retrieve to much info from the database, there is just generated too many business objects from NHibernate. Like many other I have a Facade (I call it Broker) to control the sessions and stuf like that, so I'll probably just make the "Flatten" method a part of that so it's transparent to the client.
Thanx for all your help.
Sane Productions
- Because it's too easy being insane
www.sane.dk
JayC202
Member
725 Points
145 Posts
Re: NHibernate HQL
Oct 21, 2005 02:44 PM|LINK
Alternatively you could do "SELECT c FROM Course c WHERE EXISTS (SELECT e FROM Enrollment e WHERE e.Course = c AND e.UserType = :student)".
Both of these will work. Try it. Note I just wrote these so there may be some syntax errors on your first try.