HQL - Not getting the picture when it comes to collections

ericfkaufman · **Posted:** Mon Nov 26, 2007 5:11 pm

Hibernate version: 2.2

Name and version of the database you are using: MS SQL 2005

Trying to wrap my mind around HQL queries.

I have Client, which has a collection (bag) of Policy. The property is called Policies (creative, I know). Policy has a value called Premium, which is an integer. In SQL, I want to write something like:

Select * from Client c
Inner Join Policy p
On p.ClientID = c.ClientID
WHERE p.Premium > 7

I'm not having problems writing a HQL query where I need to search by one-to-one properties. But I'm missing the boat on how to collections. Maybe another example is Doctor, and Doctor has a collection of Patient (called Patients).

How do I write an HQL query to get a list of Doctors back that have patients over the age of 50? Some generic psuedo code, or an example artile would be most appreciated!

Nels_P_Olsen · **Posted:** Mon Nov 26, 2007 6:15 pm

When you join to a collection property, understand that the alias you give it should be singular, not plural. You are actually joining to a particular member of the collection.

Here are some examples:

Code:

SELECT DISTINCT Doctor
FROM Doctor AS Doctor
JOIN Doctor.Patients AS Patient
WHERE Patient.Age > 50

or

Code:

FROM Doctor AS Doctor
WHERE EXISTS (
FROM Doctor AS Doctor2
JOIN Doctor2.Patients AS Patient2
WHERE Doctor2 = Doctor
AND Patient2.Age > 50)

If you do something like

Code:

FROM Doctor AS Doctor
JOIN Doctor.Patients AS Patient

and you don't filter the patient down to a unique one within the collection, the "rows" (object arrays) in the result set will repeat the same doctor with each distinct patient.

ericfkaufman · **Posted:** Mon Nov 26, 2007 6:47 pm

Thanks so much, I actually managed to figure it out. But you're right, that's the technique. Here's what I came up with:

Select cl from Library.NameSpace.Client cl
inner join cl.Policies as p
left join p.Supporters s
left join p.SalesPeople sp
where s.Guid = '" + e.Guid + "'"
or sp.Guid = '" + e.Guid + "'"

Nels_P_Olsen · **Posted:** Mon Nov 26, 2007 7:16 pm

Another suggestion -- use query parameters rather than embed string values directly into your HQL:

Code:

string hqlText = @"
FROM SomeEntity AS E
WHERE E.SomeProperty = :myParameter
";

IQuery myQuery = mySession.CreateQuery(hqlText);
myQuery.SetParameter(":myParameter", someValue);